sql – 授予对另一个db的用户/角色的访问权限
简短版本:我可以将外部数据库的访问权限授予角色吗?
长版: 我正在处理使用Crystal的报告,Crystal正在从应用程序SQL Server实例(database1)中检索数据. 应用程序正在运行报告并覆盖报告中的连接,我无法访问应用程序代码. 我已经向服务器(database2)添加了一个新的数据库,它正在从电话交换机收集信息,我想将这些信息加入应用程序数据(database1). 我可以在设计器中运行时加入数据和报告(以SA身份登录),但是当报告通过应用程序从外部运行时,它们会因相当一般的错误而失败(无法检索数据). 我假设错误是由新的数据库权限引起的,就像我以SA身份登录应用程序时错误消失了. 对于运行报表的用户,应用程序具有特殊的DB角色,将表/视图/ sp添加到应用程序db(database1)时,我可以简单地向此角色授予select / execute以允许报表访问对象. 现在我在不同的数据库中有对象但是这个角色不容易访问. 有没有办法可以通过现有角色引用第二个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' 要复制用户(不是登录)然后添加角色成员? 解决方法据推测,您将使用可以访问这两个数据库的登录(例如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 现在我可以连接到测试和执行 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登录(您可以将其更改为对您有意义的任何内容;窗口和SQL帐户,包含特定字符串的帐户,等等),确保已在database1和database2中创建用户,并确保它们都是添加到报告角色.您需要确保在两个数据库上都创建了报告角色,但您只需要执行一次. 之后,您可以手动或使用SQL代理作业定期运行此脚本.您需要做的就是为服务器创建登录;当脚本运行时,它将完成剩下的工作. 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,@user2 end close c deallocate c 您将需要添加事务和错误处理以滚动不完整的更改,但我会将其留给您. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |