輕鬆授予一個數據庫中的角色訪問同一伺服器上另一個數據庫中的表的權限

我有一個數據庫伺服器（Sql Server 2008 R2 Enterprise）支持兩個獨立但相關的供應商產品，每個都有自己的數據庫（稱它們為 X 和 Y）

在數據庫 XI a 中定義了很多使用者，但他們的訪問權限歸結為可管理的少數角色。我想擔任其中一個角色並使用它來授予對數據庫 Y 中的幾個表的選擇訪問權限。我真的想避免在數據庫 Y 中創建新角色並重新添加所有使用者，而不僅僅是因為忙於設置安全資訊，但也因為隨著使用者的來來去去，需要隨著時間的推移使這些角色保持同步。實際上，從業務角度來看，數據庫 X 中已經存在的角色已經很好地在邏輯上定義了我想要在數據庫 Y 中的訪問組，並且我希望保持這種狀態。

如何從 X 授予 Y 中此角色的訪問權限？

數據庫角色是完全包含在各自數據庫中的安全主體，對其他數據庫不共享或不可見。因此，數據庫 X 中的任何角色和使用者都不了解數據庫 Y。為了實現您的目標，您需要在數據庫 Y 中重新創建角色並將所有適當的使用者添加到此數據庫和角色中。幸運的是，您可以使用系統視圖編寫此過程的腳本：

USE [Y];
CREATE ROLE foo;

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
      'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
       from x.sys.database_principals where type = 'R') r
   join x.sys.database_role_members rm 
       on (r.principal_id = rm.role_principal_id)
   join (select name,type_desc,principal_id
       from x.sys.database_principals where type != 'R') u 
       on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

然後，您可以將此類腳本/流程用於批處理作業以保持同步，但您將需要某種外部流程來保持這些角色同步。

這裡可能令人困惑的一點是，您擁有連接到數據庫主體的登錄名和其他伺服器級主體，但它們之間有明顯的區別。我在這個答案中提供了一些關於這些原則之間差異的解釋

如果您使用 Windows 身份驗證，則可以將域使用者添加到域組中，並將該組添加為 SQL Server 的登錄名。然後為該登錄名提供相關數據庫的所需權限。

引用自：https://dba.stackexchange.com/questions/57300