一尘不染

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

sql

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

长版：

我正在使用Crystal处理报表，该报表正在从应用程序SQL Server实例（database1）中检索数据。

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

我已在服务器（database2）中添加了一个新的数据库，该数据库正在从电话交换机中收集信息，我想将其中的一些信息与应用程序数据（database1）相结合。

当在设计器中运行（作为SA登录）时，我可以加入数据，并且报表可以工作，但是当报表通过应用程序在外部运行时，它们将失败，并出现相当普遍的错误（无法检索数据）。

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

对于运行报告的用户，该应用程序具有特殊的数据库角色，将表/视图/
sp添加到应用程序数据库（database1）时，我可以简单地将选择/执行授予该角色，以允许报告访问该对象。

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

有什么方法可以database2通过现有角色引用第二个db（）？

例如：

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'

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

阅读 124

2021-05-16

共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

当然，您可以将所需的存储过程的授予权限更改为EXECUTE，但是看来您已经了解了这一点。

之后，只需执行一个简单的脚本即可创建登录名，用户并将其添加到角色中。

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

您将需要添加事务和错误处理以完成不完整的更改，但是我将由您自己决定。

2021-05-16