修正 SQL Server 的孤兒使用者(orphaned users)

References:

• How to fix orphaned SQL Server users
• How to troubleshoot orphan users in SQL Server databases?
• http://support.microsoft.com/kb/q274188/

問題陳述

要在 DB 中新增使用者，出現使用者在目前的資料庫中已經存在的錯誤訊息，但在該DB的使用者列表中卻沒有該使用者。

原因

在SQL server 2000，登入與使用者之間會以安全識別碼 (SID) 來建立關聯。其中登入是指用來進行使用者驗證，而資料庫使用者帳戶則是用於資料庫存取和使用權限驗證。要存取 SQL Server 伺服器則需要使用登入。驗證特定登入是否有效的程序稱為「驗證」，而這個登入必須與某一 SQL Server 資料庫使用者相關。您使用使用者帳戶來控制在資料庫中執行的活動，如果某特定登入在資料庫中沒有使用者帳戶，則使用該登入的使用者便無法存取該資料庫，就算該使用者可以連接到該 SQL Server 伺服器，也是如此。這種情況的唯一例外是當資料庫包含「guest」使用者帳戶時，沒有相關使用者帳戶的登入，會被對應到 guest 使用者；相反地，如果有資料庫使用者，但沒有相關的登入，則使用者也無法登入 SQL Server 伺服器。

CHECK:

在 MASTER 下，執行下述查詢指令

select * from sysxlogins

在要處理的 DB 下，執行下述查詢指令

select * from sysusers

可以看出兩者所查詢的 SID 不一致。

修正

在要處理的 DB 下, 執行下述指令會列出所有該DB的使用者

sp_change_users_login 'report'

執行下述指令則可修正該DB的使用者問題

sp_change_users_login 'auto_fix','userid'