在 SQL Server 中管理孤立用戶：全面指南

管理 SQL Server 中的孤立使用者對於資料庫安全性和管理至關重要。孤立使用者是指資料庫中存在使用者，但在主資料庫中卻沒有對應登入帳號的情況。這種不匹配可能會中斷存取並導致潛在的安全漏洞。在本文中，我們將探討什麼是孤立使用者，如何偵測它們以及解決它們的方法，包括更新的範例和情境。

背景

要連線到 SQL Server 資料庫，使用者必須在主資料庫中擁有有效的登入帳號。此登入帳號驗證使用者並對應到資料庫使用者以進行授權。對應使用安全識別碼 (SID)維護，確保資料庫使用者和伺服器登入帳號正確連結。

登入使用者對應規則的例外情況包括：

1. 內含式資料庫使用者：在資料庫層級進行驗證，消除了對伺服器登入帳號的依賴。這些使用者增強了資料庫的可移植性，但需要為每個資料庫單獨重新建立。
2. 訪客帳號：預設啟用，允許使用者在沒有明確對應的情況下存取，但通常基於安全原因而不建議使用。
3. Microsoft Windows 群組成員資格：如果將 Windows 群組新增為使用者，則 Windows 群組成員可以存取資料庫。

資料庫使用者在以下情況下會變成孤立使用者：

• 對應的登入帳號已刪除。
• 資料庫已遷移或還原，但沒有相關的登入帳號。
• 用戶與登錄的 SID 對應不正確。

什麼是孤立用戶？

孤立用戶發生在以下情況：

• 數據庫用戶存在，但主數據庫中沒有對應的 SQL Server 登錄。
• 數據庫用戶 SID 和登錄 SID 不匹配。

這些情況會干擾身份驗證和授權，導致錯誤或限製訪問。

檢測孤立用戶

對於 SQL Server

使用以下查詢來識別孤立用戶：

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

對於 Azure SQL 數據庫或 Synapse 分析

1. 獲取主數據庫中登錄的 SID：

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. 獲取目標數據庫中的用戶 SID：

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. 比較列表以識別不匹配。

解決孤立用戶

1. 重新創建缺失的登錄及其 SID 

如果登錄已被刪除：

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. 將孤立用戶映射到現有登錄 

如果登錄存在但尚未映射：

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. 更改用戶密碼 

在重新創建登錄後，更新其密碼：

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

使用 sp_FindOrphanedUser

存儲過程 sp_FindOrphanedUser 是檢測和解決孤立用戶的強大工具。它提供：

1. 孤立用戶的列表。
2. 自動生成的修復腳本。
3. 有關孤立用戶擁有的架構的詳細信息。

從 sp_FindOrphanedUser 獲取包代碼。

執行程序

默認執行：

EXEC sp_FindOrphanedUser;

針對特定數據庫：

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

孤立用戶場景的示例

示例 1：沒有登錄的用戶

場景

登錄被刪除，導致數據庫用戶成為孤立。

• 檢測孤立用戶：
  MS SQL

  EXEC sp_FindOrphanedUser;

• 重新建立登錄：
  MS SQL

  CREATE LOGIN [User1] WITH PASSWORD = 'SecurePassword123';

  USE [ExampleDB];

  ALTER USER [User1] WITH LOGIN = [User1];

範例 2：SID 不符

情境

登入 SID 與使用者 SID 不符。

• 檢測不匹配：
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• 修正 SID 映射：
  MS SQL

   

  使用 [ExampleDB];

  執行 sp_change_users_login '更新_ONE', '使用者2', '使用者2';

示例3：擁有模式的孤立用戶

情境

孤立用戶阻止模式刪除。

• 檢測架構所有權：
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• 重新指派架構擁有權：
  MS SQL

   

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

最佳做法

• 使用包含式資料庫使用者：避免依賴伺服器登入。
• 同步登入：在資料庫遷移期間始終遷移或還原登入。
• 定期稽核：定期檢查離散使用者以維護安全性。

結論

管理離散使用者可確保資料庫安全性和運營效率。通過使用查詢和像sp_FindOrphanedUser這樣的工具，您可以快速識別並解決問題，為合法使用者保持無縫訪問。採用像包含式使用者和定期稽核這樣的最佳做法可以預防未來出現此類問題。