批次修復 SQL Server 2005 孤立使用者單行指令

情境

資料庫伺服器遷移時，將資料庫檔案 (MDF/LDF) 附加至新伺服器，原有的資料庫使用者會變成「孤立使用者」——雖然在新伺服器建立了相同名稱的登入，但 SID 不匹配，無法正常存取。

解決方案

使用未文件化預存程序 sp_msforeachdb 迴圈執行 sp_change_users_login 'Auto_Fix'，單行指令即可批次修復所有資料庫的指定登入：

EXEC sp_msforeachdb 'USE [?]; EXEC sp_change_users_login ''Auto_Fix'', ''loginName''';

參數說明

• sp_msforeachdb：對每個使用者資料庫執行命令，? 會自動替換為資料庫名稱。
• sp_change_users_login 'Auto_Fix', 'loginName'：自動將資料庫內的孤立使用者對應至同名的伺服器登入；若登入不存在會自動建立 (需 sysadmin 權限)。

進階用法：修復所有孤立使用者

若不想指定單一登入，可先查出所有孤立使用者再逐一修復：

-- 查詢所有資料庫的孤立使用者
EXEC sp_msforeachdb '
    USE [?];
    SELECT DB_NAME() AS DBName, dp.name AS UserName, dp.type_desc
    FROM sys.database_principals dp
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
    WHERE dp.type IN (''U'', ''S'', ''G'') AND sp.sid IS NULL AND dp.name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'');
';

-- 依查詢結果逐一執行 Auto_Fix

注意事項

1. 需 sysadmin 固定伺服器角色權限。
2. Auto_Fix 會嘗試建立登入，若密碼原則嚴格可能失敗；建議先手動建立登入再執行。
3. 含有資料庫擁有者 (dbo) 的孤立使用者需特別處理：EXEC sp_changedbowner 'loginName'。
4. SQL Server 2005+ 建議改用 ALTER USER ... WITH LOGIN = ... 正式語法。

延伸閱讀

• sp_change_users_login (官方文件)
• ALTER USER (官方文件)