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

適用于:yesSQL Server (所有支援的版本,) Yes Azure SQL Database Yes Azure SQL 受控實例 yes Azure Synapse Analytics yes Platform System (PDW)

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

注意

針對可能會移動的資料庫,使用自主資料庫使用者可減少被遺棄使用者產生的可能性。 如需詳細資訊,請參閱 自主的資料庫使用者 - 使資料庫可攜

背景

若要使用安全性主體連接到 SQL Server 實例上的資料庫, (資料庫使用者身分識別) 以登入為基礎,主體必須在 master 資料庫中擁有有效的登入。 此登入用於驗證主體身分識別的驗證程式中,並判斷主體是否允許連線到 SQL Server 的實例。 伺服器實例上的 SQL Server 登入會顯示在 sys.server_principals 目錄檢視和 sys.sql_logins 相容性檢視中。

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

  • 自主資料庫使用者

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

  • guest 帳戶。

    在資料庫中啟用時,此帳戶允許未對應到資料庫使用者的 SQL Server 登入,以 來賓 使用者身分輸入資料庫。 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>';  

重要

任何登入都可以變更其密碼。 只有具有 ALTER ANY LOGIN 權限的登入,才能夠變更其他使用者登入的密碼。 不過,只有 sysadmin 角色成員才能修改 sysadmin 角色成員的密碼。

另請參閱

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)