共用方式為

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

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

當資料庫使用者以資料庫中的 master 登入為基礎，但登入不再存在 master於 時，使用者在 SQL Server 中會成為孤立者。 當登入被刪除或資料庫移至登入不存在的另一部伺服器時，可能會發生這種情況。 本文說明如何尋找孤立的使用者，並將他們重新對應至登入。

注意

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

背景

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

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

  • 自主資料庫使用者

    自主資料庫使用者會在使用者資料庫層級進行驗證，而且不會與登入相關聯。 建議使用此模型，因為資料庫更具可攜性，而且自主資料庫使用者不會成為孤立者。 不過，必須針對每一個資料庫重新建立它們。 此模型在具有許多資料庫的環境中可能不切實際。

  • 訪客帳戶

    在資料庫中啟用時，此帳戶允許未對應至資料庫使用者的 SQL Server 登入以 來賓 使用者身分存取資料庫。 guest 帳戶預設為停用。

  • Microsoft Windows 群組成員資格

    如果 Windows 使用者是 Windows 群組的成員，且也是資料庫中的使用者，則從 Windows 使用者建立的 SQL Server 登入可以存取資料庫。

SQL Server 登入與資料庫使用者對應的相關資訊會儲存在資料庫中。 它包含資料庫使用者的名稱，以及對應 SQL Server 登入的安全性識別碼 （SID）。 此資料庫使用者的權限適用於資料庫中的授權。

未定義對應 SQL Server 登入或在伺服器執行個體上定義不正確的資料庫使用者 （以登入為基礎） 無法登入執行個體。 這類使用者就是伺服器執行個體上的資料庫 「被遺棄使用者」 (Orphaned User)。 如果資料庫使用者對應至資料庫中master不存在的登入SID，則可能會發生孤立。 在資料庫還原或附加到其他未建立登入的 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。

針對 Azure SQL 資料庫和 Azure Synapse Analytics

sys.server_principals資料表無法在 SQL Database 或 Azure Synapse Analytics 中使用。 完成下列步驟，識別這些環境中的孤立使用者：

  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資料表中是否有與資料庫sql_logins資料表中的master登入 SID 不相符的使用者 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 角色成員的密碼。

相關內容

  • Last updated on