查詢、偵測「孤兒使用者(Orphaned Users)」、「被遺棄使用者」

有關 SQL Server 登入對應到資料庫使用者的資訊會儲存在資料庫內。
它包括資料庫使用者的名稱和對應之 SQL Server 登入的 SID。

此資料庫使用者的權限使用於資料庫中的授權。

在伺服器執行個體上未定義或不正確定義對應之 SQL Server 登入的資料庫使用者無法登入此執行個體。
這類使用者就是伺服器執行個體上的資料庫「被遺棄使用者」。

如果卸除了對應的 SQL Server 登入，則資料庫使用者可能遭到遺棄。
此外，資料庫還原或附加到其他 SQL Server 執行個體，也會讓資料庫使用者遭到遺棄。

如果資料庫使用者對應到的 SID 未出現在新的伺服器執行個體中，則會遭到遺棄。

在預存程序：sp_change_users_login 上提供了 @Action='Report' 引數，可以用來偵測被遺棄使用者。
也提供了 @Auto_Fix 引數，可以用來將目前資料庫中 sys.database_principals 系統目錄檢視的使用者項目連結到相同名稱的 SQL Server 登入。

但是在 SQL Server 線上叢書內，說明此預存程序的功能將來可能會被移除。

sp_change_users_login (Transact-SQL) 
將現有的資料庫使用者對應至 SQL Server 登入。未來的 Microsoft SQL Server 版本將移除這項功能。
請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 請改用 ALTER USER。

--00

---

(1) 使用「目錄檢視」來查詢孤兒使用者

-- 使用「目錄檢視」來查詢孤兒使用者
-- 適用版本：SQL Server 2005、2008、2008 R2

SELECT dpr.name N'使用者', dpr.sid N'SID(安全性識別碼)', dpr.type_desc N'主體類型', dpr.create_date N'建立日期',
 dpr.modify_date N'修改日期',dpr.default_schema_name N'預設的結構描述'
FROM sys.server_principals spr RIGHT OUTER JOIN sys.database_principals dpr
 ON spr.sid =dpr.sid
WHERE dpr.type IN ('S','U','G') AND dpr.sid NOT IN(0x01,0x00)

--01

---

(2) 使用「sp_change_users_login」來查詢孤兒使用者

-- 使用「sp_change_users_login」來查詢孤兒使用者
-- 適用版本：SQL Server 2000、2005、2008、2008 R2

EXEC sp_change_users_login @Action='Report';
GO

--02

---

參考資料：
「孤兒使用者疑難排解(Troubleshooting Orphaned Users )」
http://msdn.microsoft.com/zh-tw/library/ms175475.aspx

sp_change_users_login (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms174378.aspx

ALTER USER (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms176060.aspx