SQL Server 孤立使用者完整解析：成因、診斷、修復與預防最佳實務

前言：資料庫遷移後的「隱形殺手」

將資料庫備份還原或分離附加至新實例時，伺服器層級登入 (Login) 與資料庫層級使用者 之間的 SID (Security Identifier) 失去對應，導致原本正常的登入無法存取資料庫，拋出經典錯誤：

• Msg 229, Level 14, State 1: %s permission denied on object %.*s, database %.*s, owner %.*s
• Error 15023: User or role '%s' already exists in the current database.

本文系統說明成因、診斷查詢、標準修復腳本、預防機制與自動化方案。

核心成因：SID 不匹配

還原/附加資料庫時，sys.database_principals 隨資料庫檔案移動，但 master.sys.server_principals 留在原實例。新實例若建立同名 Login，SID 為新產生，與資料庫內既有 User SID 不符 → **孤立使用者**。

診斷查詢：快速識別所有孤立使用者

-- 方法 1: 系統預存程序 (舊版相容)
EXEC sp_change_users_login 'Report';

-- 方法 2: 現代目錄檢視 (推薦，資訊更豐富)
SELECT
    dp.name AS DatabaseUser,
    dp.type_desc AS UserType,
    dp.sid AS UserSID,
    sp.name AS ServerLogin,
    sp.sid AS LoginSID,
    CASE WHEN sp.sid IS NULL THEN 'Orphaned' ELSE 'Mapped' END AS Status
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')  -- SQL, Windows User, Windows Group
  AND dp.principal_id > 4;        -- 排除系統內建

標準修復方案三選一

方案 A：sp_change_users_login (舊版、單一使用者、自動建立 Login)

-- 1. 回報孤立使用者
EXEC sp_change_users_login 'Report';

-- 2. 自動修復：若 Login 不存在則建立 (需 sysadmin)
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'StrongPassword';

-- 3. 僅對應既有同名 Login (需 Login 已存在)
EXEC sp_change_users_login 'Update_One', 'UserName', 'LoginName';

限制： 僅支援 SQL 登入，不支援 Windows 登入/群組；已棄用，未來版本移除。

方案 B：ALTER USER ... WITH LOGIN (現代標準、支援所有類型、不建立 Login)

-- 前提：目標 Login 已存在於新實例 (同名或不同名皆可)
ALTER USER [DatabaseUser] WITH LOGIN = [ServerLogin];
-- 或指定 SID (極少用)
ALTER USER [DatabaseUser] WITH LOGIN = [ServerLogin], SID = 0x...;

• 支援 SQL Login、Windows User、Windows Group、含憑證/非對稱金鑰的使用者。
• 不會建立 Login，需預先在伺服器層級建立對應 Login。
• SQL Server 2005 SP2+ 完整支援。

方案 C：重建使用者 (徹底但代價高：失去權限、擁有權、Schema 綁定)

-- 僅在無法對應時最後手段
DROP USER [OrphanedUser];
CREATE USER [NewUser] FOR LOGIN [ServerLogin];
-- 之後必須：重新授權、修正 Schema 擁有權、重建資料庫角色成員資格

Windows 登入/群組孤立使用者特殊處理

Windows 登入的 SID 來源於 AD/本機 SAM，**跨網域/重建網域/移轉網域** 時 SID 會改變，導致大量孤立使用者。

• 無法使用 sp_change_users_login。
• 必須在新實例建立對應 Windows 登入 (`CREATE LOGIN [DOMAIN\User] FROM WINDOWS;`)。
• 再用 ALTER USER ... WITH LOGIN 對應。
• 若網域 SID 整體變更，需批次腳本對應 (參考 sp_helprevlogin 產生建立腳本)。

預防與自動化：從源頭杜絕

1. 統一 Login 建立策略：使用 sp_helprevlogin 同步 SID

Microsoft 提供 sp_helprevlogin 預存程序，能產生包含 **原始 SID、密碼雜湊、預設資料庫、語言** 的 CREATE LOGIN 腳本。遷移前在舊實例執行，遷移後在新實例執行腳本，**Login SID 完美保留**，資料庫 User 即自動對應，零孤立使用者。

2. 含檔資料庫 - 徹底解決方案

-- 建立/轉換為含檔資料庫
ALTER DATABASE MyDB SET CONTAINMENT = PARTIAL;
-- 建立含檔使用者 (無伺服器層級 Login，密碼存資料庫內)
CREATE USER AppUser WITH PASSWORD = 'StrongPwd';
-- 遷移/還原至任意實例即可直接連線，零 SID 問題

適用場景： 多租戶、頻繁遷移、CI/CD 環境、Always On 可用性群組次要複本可讀性。

3. 自動化遷移檢查清單 (PowerShell + dbatools)

# 使用 dbatools 模組
Copy-DbaLogin -Source OldServer -Destination NewServer -ExcludeSystemLogins
# 自動同步 SID、密碼雜湊、權限、伺服器角色

Test-DbaDbOrphanUser -SqlInstance NewServer -Database MyDB
# 驗證是否仍有孤立使用者

總結：決策流程圖

1. 遷移前：執行 sp_helprevlogin 產生 Login 建立腳本。
2. 遷移後：在新實例執行腳本建立 Login (SID 同步)。
3. 驗證：執行 Test-DbaDbOrphanUser 或 sp_change_users_login 'Report'。
4. 若仍有孤立 (如 Windows 登入跨網域)：
   • 建立對應 Login → ALTER USER ... WITH LOGIN。
   • 或轉為含檔資料庫使用者。
5. 納入 CI/CD Pipeline 自動化驗證。