SQL Server 孤立用戶完整解決方案：sp_change_users_login 與含資料庫用戶遷移最佳實踐

前言：備份還原後的「隱形殺手」

跨伺服器備份還原資料庫時，資料庫內的用戶與伺服器層級的登入因 SID 不匹配而斷裂，形成「孤立用戶」。應用連線報錯 `Login failed` 或 `User already exists`，實為 SID 不符。本文提供從偵測、修復、預防的完整閉環方案，涵蓋 `sp_change_users_login`、Contained Database User、遷移腳本自動化。

一、核心概念：登入 vs 用戶 vs SID

孤立用戶定義：資料庫用戶存在，但 `sys.database_principals.sid` 在 `sys.server_principals` 中找不到對應 SID (SQL Login)；或 Windows 登入 SID 在新伺服器不存在。

二、偵測孤立用戶

1. 經典方法 (相容所有版本)

USE YourDB;
EXEC sp_change_users_login 'Report';
-- 回傳：UserName, UserSID

2. 現代查詢 (SQL 2005+、資訊更豐富)

SELECT
    dp.name AS DatabaseUser,
    dp.type_desc AS UserType,
    dp.sid AS UserSID,
    sp.name AS ServerLogin,
    sp.type_desc AS LoginType,
    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_USER, WINDOWS_USER, WINDOWS_GROUP
  AND dp.principal_id > 4;        -- 排除系統用戶

三、修復策略對比

四、含資料庫用戶遷移最佳實踐 (SQL 2012+)

根治孤立用戶的終極方案：Contained Database User (包含資料庫用戶)。用戶認證資訊 (密碼雜湊) 直接儲存在資料庫內，無需伺服器層級登入，跨伺服器還原即可直接連線，零孤立風險。

1. 啟用含資料庫認證

-- 伺服器層級
EXEC sp_configure 'contained database authentication', 1; RECONFIGURE;

-- 資料庫層級
ALTER DATABASE YourDB SET CONTAINMENT = PARTIAL;

2. 建立/遷移為含資料庫用戶

-- 新建
CREATE USER AppUser WITH PASSWORD = 'ComplexP@ssw0rd!', DEFAULT_SCHEMA = dbo;

-- 從 SQL Login 遷移 (SQL 2012+)
-- 步驟 1: 建立同名含資料庫用戶 (密碼同步)
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += 'CREATE USER ' + QUOTENAME(name) + ' WITH PASSWORD = ''Migrate_' + name + '_' + CONVERT(NVARCHAR(36), NEWID()) + ''', DEFAULT_SCHEMA = ' + QUOTENAME(default_schema_name) + ';'
FROM sys.database_principals
WHERE type = 'S' AND principal_id > 4; -- SQL 用戶
EXEC sp_executesql @sql;

-- 步驟 2: 移除舊用戶 (權限已由含資料庫用戶繼承)
-- 注意：需先轉移結構擁有權
EXEC sp_change_users_login 'Report'; -- 確認無孤立
-- 批次轉移結構擁有權 (若有)
-- 最後 DROP 舊用戶

3. 連線字串調整

-- 原來
Server=OldServer;Database=YourDB;User Id=AppUser;Password=...;

-- 含資料庫用戶 (同樣語法，但驗證發生在資料庫層級)
Server=NewServer;Database=YourDB;User Id=AppUser;Password=...;
-- 無需在 NewServer 建立 Login！

五、自動化遷移腳本 (跨伺服器還原後執行)

-- usp_FixOrphanedUsers_PostRestore
CREATE PROCEDURE dbo.usp_FixOrphanedUsers_PostRestore
    @TargetLoginPrefix NVARCHAR(50) = N''  -- 若登入名稱有前綴差異
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    -- 1. 偵測孤立 SQL 用戶
    DECLARE @Orphans TABLE (DbUser SYSNAME, DbSID VARBINARY(85), LoginName SYSNAME);
    INSERT INTO @Orphans
    SELECT dp.name, dp.sid, sp.name
    FROM sys.database_principals dp
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
    WHERE dp.type = 'S' AND dp.principal_id > 4 AND sp.sid IS NULL;

    -- 2. 嘗試按名稱匹配現有登入 (含前綴)
    UPDATE o SET o.LoginName = sp.name
    FROM @Orphans o
    JOIN sys.server_principals sp ON sp.name = @TargetLoginPrefix + o.DbUser
    WHERE sp.type = 'S';

    -- 3. 逐一修復 (Update_One)
    DECLARE @DbUser SYSNAME, @LoginName SYSNAME;
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT DbUser, LoginName FROM @Orphans WHERE LoginName IS NOT NULL;
    OPEN cur;
    FETCH NEXT FROM cur INTO @DbUser, @LoginName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            EXEC sp_change_users_login 'Update_One', @DbUser, @LoginName;
            PRINT 'Fixed: ' + @DbUser + ' -> ' + @LoginName;
        END TRY
        BEGIN CATCH
            PRINT 'FAILED: ' + @DbUser + ' - ' + ERROR_MESSAGE();
        END CATCH
        FETCH NEXT FROM cur INTO @DbUser, @LoginName;
    END
    CLOSE cur; DEALLOCATE cur;

    -- 4. 報告剩餘無法自動修復的
    SELECT * FROM @Orphans WHERE LoginName IS NULL;
END;

六、Windows 登入孤立處理

• Windows 登入 SID 由 AD 決定，跨網域遷移 SID 必變
• 無法用 `sp_change_users_login` 修復 (僅限 SQL Login)
• 解決：`ALTER USER db_user WITH LOGIN = [NEWDOMAIN\user];` 或重建用戶映射新登入
• 最佳：遷移至含資料庫用戶 (密碼認證) 或統一使用 SQL Login 遷移

七、預防清單：遷移前必做

八、總結

孤立用戶本質是 SID 身分識別斷裂。短期用 `ALTER USER ... WITH LOGIN` 精確修復；長期必須推行 Contained Database User 徹底消除跨伺服器身分依賴。遷移自動化腳本 + `sp_help_revlogin` 匯出登入 + 含資料庫認證，構成「零孤立」鐵三角。