針對孤立使用者進行疑難排解 (SQL Server)

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

SQL Server 中的孤立使用者，會在資料庫使用者是根據 master 資料庫中的登入，但該登入已不存在於 master 中時發生。 當登入遭到刪除，或當資料庫移動到另一個登入不存在的資料庫時，就可能發生。 本主題說明如何尋找被遺棄使用者，並將他們重新對應至登入。

背景

若要使用以登入為基礎的安全性主體 (資料庫使用者身分識別) 連線到 SQL Server 執行個體上的資料庫，主體在 master 資料庫必須要有有效的登入。 此登入適用於驗證程序，可確認主體身分識別並決定主體是否允許連線到 SQL Server 的執行個體。 伺服器執行個體上的 SQL Server 登入可以在 sys.server_principals 目錄檢視和 sys.sql_logins 相容性檢視中看到。

SQL Server 登入會以對應到 SQL Server 登入的「資料庫使用者」身分來存取個別資料庫。 這項規則有三個例外狀況：

• 自主資料庫使用者

  自主資料庫使用者驗證位於使用者資料庫層級，且與登入沒有關連。 這是建議項目，因為資料庫會有更佳的可攜性，且自主資料庫使用者不會成為被遺棄使用者。 不過，它們也必須在每個資料庫上重新建立。 這在具有許多資料庫的環境中並不實用。

• guest 帳戶。

  在資料庫中啟用時，此帳戶會使未對應到資料庫使用者的 SQL Server 登入都能以 guest 使用者的身分進入資料庫。 guest 帳戶預設為停用。

• Microsoft Windows 群組成員資格。

  如果某個 Windows 使用者是同時也是該資料庫使用者之 Windows 群組的成員，則從該 Windows 使用者建立的 SQL Server 登入也可以進入資料庫。

有關 SQL Server 登入對應到資料庫使用者的資訊會儲存在資料庫內。 其包括資料庫使用者的名稱和對應 SQL Server 登入的 SID。 此資料庫使用者的權限適用於資料庫中的授權。

在伺服器執行個體上未定義或定義錯誤之 SQL Server 登入的對應資料庫使用者 (以登入為基礎) 將無法登入此執行個體。 這類使用者就是伺服器執行個體上的資料庫 「被遺棄使用者」 (Orphaned User)。 如果資料庫使用者對應到的登入 SID 未出現在 master 執行個體中，則會遭到遺棄。 在資料庫還原或附加到其他未建立登入的 SQL Server 執行個體之後，資料庫使用者也可能會變成孤立。 如果卸除了對應的 SQL Server 登入，則資料庫使用者也會變成孤立。 即使重新建立登入，它的 SID 也會不同，因此資料庫使用者仍會遭到遺棄。

偵測被孤立使用者

適用於 SQL Server 和 PDW

若要根據遺失的 SQL Server 驗證登入來偵測 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';  

輸出會列出目前資料庫中未與任何 SQL Server 登入相連結的 SQL Server 驗證使用者及對應的安全性識別碼 (SID)。

SQL Database 和 Azure Synapse Analytics

SQL Database 或 Azure Synapse Analytics 不提供 sys.server_principals 資料表。 請執行下列步驟來識別這些環境中的孤立使用者：

1. 連接到 master 資料庫，然後使用下列查詢選取登入的 SID：

   SELECT sid 
   FROM sys.sql_logins 
   WHERE type = 'S'; 
   

2. 連接到使用者資料庫，然後使用下列查詢檢閱 sys.database_principals 資料表中的使用者 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. 比較這兩份清單，以判斷使用者資料庫 sys.database_principals 資料表中是否有使用者 SID 不符合 master 資料庫 sql_logins 資料表中的登入 SID。

解決孤立使用者

在 master 資料庫中，使用 CREATE LOGIN 陳述式搭配 SID 選項以重新建立遺失的登入，提供在上一節取得之資料庫使用者的 SID ：

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

若要將孤立使用者對應到已存在 master中的登入，請在使用者資料庫中執行 ALTER USER 陳述式，並指定登入名稱。

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

當您重新建立遺失的登入時，使用者可以使用提供的密碼存取資料庫。 接著使用者可以使用 ALTER LOGIN 陳述式改變登入帳戶的密碼。

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

另請參閱

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_loginssys.syslogins (Transact-SQL)