将一个数据库的访问权限授予另一个用户/角色 [英] Granting access to one db to users/roles of another

问题描述

简短版本:我可以向角色授予对外部数据库的访问权限吗?

Short version: Can I grant access to external databases to a role?

长版:

我正在使用 Crystal 处理报告，它从应用程序 SQL Server 实例 (database1) 中检索数据.

I am working on reports using Crystal which is retrieving data from an applications SQL Server Instance (database1).

应用程序正在运行报告并覆盖报告中的连接，我无法访问应用程序代码.

The application is running the reports and overwriting the connection in the report, I have no access to the applications code.

我向服务器 (database2) 添加了一个新数据库，该数据库从电话交换机收集信息，我想将其中一些信息加入应用程序数据 (database1)代码>).

I have added a new DB to the server (database2) which is collecting information from a telephone exchange and I want to join some of this information to the applications data (database1).

在设计器中运行时，我可以加入数据和报告工作(以 SA 身份登录)，但是当报告通过应用程序在外部运行时，它们会失败并出现相当普遍的错误(无法检索数据).

I can join the data and the reports work when run within the designer (logged in as SA) but when the reports are run externally through the application they fail with a fairly generic error (Failed to retrieve data).

我假设错误是由新的数据库权限引起的，就好像我以 SA 身份登录应用程序一样，错误消失了.

I am assuming the error is being caused by the new DB permissions as if I log into the application as SA the error goes away.

应用程序对运行报告的用户有一个特殊的数据库角色，当向应用程序数据库 (database1) 添加表/视图/sp 时，我可以简单地将选择/执行授予此角色以允许访问对象的报告.

The Application has a special DB Role for users that run reports, when adding a table/view/sp to the application db (database1) I can simply grant select/execute to this role to allow the reports to access the object.

现在我在不同的数据库中有对象，但是这个角色不容易访问.

Now I have object in a different db however the role isn't easily accessible.

有什么办法可以通过现有角色引用第二个数据库(database2)?

Is there any way I can reference the second db (database2) through the existing role?

例如:

USE [database1]
GRANT EXECUTE ON [database2].[dbo].[CUSTOM_PROCEDURE] TO [applicationrole1] 

OR

USE [database2]
GRANT EXECUTE ON [dbo].[CUSTOM_PROCEDURE] TO [database1].[dbo].[applicationrole1]

理想情况下，我希望能够以某种方式链接到角色，而不是重新创建新角色，因为在添加/配置新用户时应用程序会定期更新角色.

Ideally I want to be able to link to the Role somehow rather than re-creating a new role as the role is updated by the application regularly when new users are added/configured.

(没有用 Crystal-Reports 标记，因为这与问题无关)

有什么办法可以做到:

INSERT INTO Database2.sys.database_principals
SELECT * FROM Database1.sys.database_principals
WHERE [type] = 'S'

复制用户(不是登录)然后添加角色成员?

To copy over the Users (not logins) and then add the role members?

推荐答案

大概，您将使用可以访问两个数据库的登录名(例如 SA 的情况).您需要创建适当的角色并授予每个数据库的权限，然后在两者中创建用户(链接到您使用的登录名)，将每个角色添加到您创建的角色中.

Presumably, you'd be using a login that has access to both databases (such as the case with SA). You'd create the appropriate role and grant rights to each database, then create the user (linked to the login you're using) in both, adding each to the role you created.

T-SQL 看起来像这样:

The T-SQL will look something like this:

use master
go
create login testuser with password = 'mypassword123'
go

use test
go

create role reporting
grant select on something to reporting -- grant your permissions here

create user testuser for login testuser
exec sp_addrolemember 'reporting', 'testuser'
go

use test2
go

create role reporting
grant select on something2 to reporting -- grant your permissions here

create user testuser for login testuser
exec sp_addrolemember 'reporting', 'testuser'
go

现在我可以连接到 test 并执行

Now I can connect to test and execute

 select * from something
 select * from test2.dbo.something2

当然，您可以在所需的存储过程上将授权更改为 EXECUTE，但看起来您已经涵盖了这一点.

Of course, you'd change your grants to EXECUTE on the desired stored procedures, but it looks like you've already got that covered.

之后，只需执行一个简单的脚本来创建登录名、用户并将它们添加到角色中.

After that, it's just about executing a simple script to create logins, users, and add them to the role.

declare @sql nvarchar(max), @username nvarchar(50), @password nvarchar(50)

-- ########## SET PARAMETERS HERE
SET @username = N'testguy'
SET @password = N'test123'
-- ########## END SET PARAMETERS

set @sql = N'USE master; CREATE LOGIN [' + @username + N'] WITH PASSWORD = N''' + @password + N'''; USE database1; CREATE USER [' + @username + N'] FOR LOGIN [' + @username + N']; EXEC sp_addrolemember ''reporting'', ''' + @username + N''';  USE database2; CREATE USER [' + @username + N'] FOR LOGIN [' + @username + N']; EXEC sp_addrolemember ''reporting'', ''' + @username + N''';'
exec sp_executesql @sql

自动同步登录名、用户和角色

此脚本将查找所有 SQL 登录名(您可以将其更改为对您有意义的任何内容；Windows AND SQL 帐户、包含特定字符串的帐户等)，确保用户已在 database1 和 database2，并确保它们都添加到 reporting 角色.您需要确保在两个数据库上都创建了 reporting 角色，但您只需要做一次.

Syncing logins, users, and roles automatically

This script will find all SQL logins (you can change this to whatever makes sense to you; windows AND SQL accounts, accounts that contain a certain string, whatever), ensure the user has been created in database1 and database2, and ensures they are both added to the reporting role. You will need to ensure the reporting role is created on both databases, but you only need to do this once.

之后，您可以手动或使用 SQL 代理作业定期运行此脚本.您需要做的就是为服务器创建登录名；当脚本运行时，它会完成剩下的工作.

After that, you can run this script periodically, either manually, or using a SQL Agent job. All you need to do is create the login for the server; when the script runs it will do the rest.

declare @login nvarchar(50), @user1 nvarchar(50), @user2 nvarchar(50), @sql nvarchar(max), @rolename nvarchar(50)

SET @rolename = 'reporting'

declare c cursor for 
select sp.name as login, dp1.name as user1, dp2.name as user2 from sys.server_principals as sp
    left outer join database1.sys.database_principals as dp1 on sp.sid = dp1.sid
    left outer join database2.sys.database_principals as dp2 on sp.sid = dp2.sid
where sp.type = 'S' 
    and sp.is_disabled = 0

open c

fetch next from c into @login, @user1, @user2

while @@FETCH_STATUS = 0 begin

    -- create user in db1
    if (@user1 is null) begin
        SET @sql = N'USE database1; CREATE USER [' + @login + N'] FOR LOGIN [' + @login + N'];'
        EXEC sp_executesql @sql
    end

    -- ensure user is member of role in db1
    SET @sql = N'USE database1; EXEC sp_addrolemember '''+ @rolename + ''', ''' + @login + N''';'
    EXEC sp_executesql @sql

     -- create user in db2
    if (@user2 is null) begin
        SET @sql = N'USE database2; CREATE USER [' + @login + N'] FOR LOGIN [' + @login + N'];'
        EXEC sp_executesql @sql
    end

    -- ensure user is member of role in db2
    SET @sql = N'USE database2; EXEC sp_addrolemember '''+ @rolename + ''', ''' + @login + N''';'
    EXEC sp_executesql @sql

    fetch next from c into @login, @user1, @user2
end


close c
deallocate c

您需要添加一个事务和错误处理来取消未完成的更改，但我会留给您.

You will want to add a transaction and error handling to roll off incomplete changes, but I'll leave that up to you.

这篇关于将一个数据库的访问权限授予另一个用户/角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！