SQL Server Restore With Orphaned User Missing User (孤兒 / 孤立使用者)

2022-01-18

說明 SQL Server 在進行資料庫還原、遷移上可能會碰到的 User 與 Login 不一致、孤立使用者、缺少使用者的問題。

說明如何以 TSQL Script 檢查目前的角色授權以及權限授權，並說明如何處理 Orphaned User，同時彙整微軟官方介紹的「在 SQL Server 實例之間傳輸登入和密碼」使用方式。

說明

檢查 User 被授予的權限 (Database User Permissions)

SELECT state_desc,
       permission_name,
       class_desc,
       Object_name(major_id) ObjectName,
       NAME,
       sid
FROM   sys.database_permissions p
       LEFT JOIN sys.sysusers u
              ON p.grantee_principal_id = u.uid
WHERE  p.grantee_principal_id != 0

檢查 User 具備的角色 (Database User Roles)

SELECT DP1.name AS DatabaseRoleName,   
   isnull (DP2.name, 'No members') AS DatabaseUserName   
 FROM sys.database_role_members AS DRM  
 RIGHT OUTER JOIN sys.database_principals AS DP1  
   ON DRM.role_principal_id = DP1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS DP2  
   ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name;

孤兒使用者 / 孤立使用者 Orphaned User

當遷移資料庫至不同伺服器時，必須重新建立 Login 同時必須要將資料庫中的 User 與 Login 做對應。

檢查孤兒使用者

USE userDatabase;
EXEC sp_change_users_login 'report';

修復孤兒使用者

USE userDatabase;
EXEC sp_change_users_login 'update_one', 'userName', 'loginName'

-- Create Login win passwords
EXEC sp_change_users_login 'update_one', 'userName', 'loginName', 'passwords'

在 SQL Server 實例之間傳輸登入和密碼

執行 Scripts 可以參考 Transfer-Login | gist.github 或者是微軟的在 SQL Server 實例之間傳輸登入和密碼。

執行 Scripts 並建立 Stored Procedures 後，可以執行下列的預存程序進行查詢，並將結果複製到目標資料庫伺服器上執行。

EXEC sp_help_revlogin

針對孤立使用者進行疑難排解 (SQL Server)