将一个数据库的访问权限授予另一个用户/角色。

Question

简短版：我是否可以向角色授予对外部数据库的访问权限？

详细版：

我正在使用Crystal报表，从应用程序SQL Server实例 (database1) 检索数据。

应用程序正在运行报表并重写报表中的连接，我无法访问应用程序代码。

我已经在服务器上添加了一个新的DB (database2)，该DB正在收集来自电话交换机的信息，我想将其中一些信息与应用程序数据 (database1) 进行连接。

我可以连接数据，并且当以设计者身份 (SA) 登录时，报表可以正常工作，但是当通过应用程序在外部运行报表时，它们会失败并显示一个相当通用的错误 (无法检索数据)。

我假设这个错误是由新的DB权限引起的，因为如果我以SA身份登录应用程序，错误就消失了。

应用程序为运行报表的用户提供了特殊的DB角色，当向应用程序DB (database1) 添加表/视图/存储过程时，我可以仅仅授予此角色的select/execute权限，以允许报表访问对象。

现在我有了第二个DB中的对象，但是该角色不容易访问。

是否有任何方法可以通过现有角色引用第二个DB (database2)？

例如：

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

OR

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

理想情况下，我希望能够以某种方式链接到角色，而不是重新创建一个新角色，因为当添加/配置新用户时，应用程序会定期更新角色。此内容与Crystal-Reports无关（未标记）。编辑：是否有任何方法可以执行以下操作：

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

复制用户（而不是登录用户），然后添加角色成员？

- bendataclear

Answer 1

假设您使用的登录帐户可以访问两个数据库（例如SA的情况）。您需要创建相应的角色并授予每个数据库权限，然后在两个数据库中创建用户（与您正在使用的登录帐户关联），将每个用户添加到您创建的角色中。

T-SQL代码如下：

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 并执行命令

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

当然，您需要将授权更改为在所需的存储过程上执行，但看起来您已经处理好了这个问题。

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

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 和 SQL 账号)，确保用户已在 database1 和 database2 中创建，然后确保它们都添加到了 reporting 角色中。您需要确保 reporting 角色在两个数据库中都被创建，但您只需要执行一次。

之后，您可以定期运行此脚本，手动或使用 SQL Agent 作业。您只需要为服务器创建登陆账号; 当脚本运行时，它将完成其余任务。

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

你需要添加事务和错误处理来回溯未完成的更改，但我会留给你自己去处理。

- moribvndvs

---

新用户非常频繁地添加，每次运行这些脚本可能很困难。有没有办法镜像登录？ - bendataclear

你必须创建用户，所以最好从一个脚本中进行操作，并一次性完成。我会同时提供该脚本。 - moribvndvs

可以将在database中的但在database2中缺失的帐户同步。我会编写一个额外的脚本。然后，您可以安排定期运行此脚本的作业，以便您只需担心创建登录即可。 - moribvndvs

我添加了之前提到的脚本。 - moribvndvs

看起来很理想，当系统离线时我会尝试一下，并让您知道是否有任何问题，感谢您的时间！ - bendataclear

Answer 2

将存储过程设置为以所有者身份执行。

http://msdn.microsoft.com/en-us/library/ms188354.aspx

在存储过程所在的数据库上设置可信任。

http://technet.microsoft.com/en-us/library/ms187861.aspx

请确保两个数据库的所有者相同。

http://msdn.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

将存储过程的执行权限授予数据库中适当的用户或角色。

- JStead

---

我已经尝试了这四个步骤，但应用程序错误仍然弹出，我还尝试在database1中创建一个调用database2中过程的存储过程，但仍然出现错误。 - bendataclear