Problembehandlung bei verwaisten Benutzern (SQL Server)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Verwaiste Benutzer treten in SQL Server auf, wenn ein Datenbankbenutzer auf einem Anmeldenamen in der Masterdatenbank basiert, aber der Anmeldename nicht mehr in Mastervorhanden ist. Dies kann auftreten, wenn der Anmeldename gelöscht wird, oder wenn die Datenbank auf einen anderen Server verschoben wird, auf dem der Anmeldename nicht existiert. Dieses Thema beschreibt, wie Sie verwaiste Benutzer finden und sie den Anmeldenamen erneut zuordnen können.

Hinweis

Verringern Sie mögliche verwaiste Benutzer, indem Sie eigenständige Datenbankbenutzer für Datenbanken, die verschoben werden können, verwenden. Weitere Informationen finden Sie unter Eigenständige Datenbankbenutzer - machen Sie Ihre Datenbank portabel.

Hintergrund

Für die Verbindung mit einer Datenbank oder einer Instanz von SQL Server mithilfe eines Sicherheitsprinzipals (Datenbank-Benutzeridentität) auf Basis eines Anmeldenamens, muss der Prinzipal einen gültigen Anmeldenamen in der Masterdatenbank besitzen. Dieser Anmeldename wird bei der Authentifizierung benötigt, bei der die Prinzipalidentität überprüft wird und bestimmt wird, ob der Prinzipal eine Verbindung mit der SQL Server-Instanz herstellen darf. Die auf einer Serverinstanz vorhandenen SQL Server -Anmeldenamen werden in der sys.server_principals -Katalogsicht und der sys.sql_logins -Kompatibilitätssicht angezeigt.

SQL Server -Anmeldenamen greifen als „Datenbankbenutzer“ auf individuelle Datenbanken zu, der dem SQL Server -Anmeldenamen zugeordnet ist. Es gibt jedoch drei Ausnahmen von dieser Regel:

• Eigenständige Datenbankbenutzer

  Eigenständige Datenbankbenutzer authentifizieren sich auf Benutzerdatenbankebene und werden keinem Anmeldenamen zugeordnet. Dies wird empfohlen, da die Datenbanken besser portierbar sind und eigenständige Datenbankbenutzer nicht verwaisen können. Sie müssen jedoch für jede Datenbank neu erstellt werden. Möglicherweise ist dies in einer Umgebung mit mehreren Datenbanken nicht sinnvoll.

• Das Gastkonto .

  Wenn dieses Konto in der Datenbank aktiviert ist, erlaubt es SQL Server -Anmeldenamen, die keinem Datenbankbenutzer zugeordnet sind, die die Datenbank als Gastbenutzer verwenden. Das Gastkonto wird standardmäßig deaktiviert.

• Microsoft Windows-Gruppenmitgliedschaften.

  Ein von einem Windows-Benutzer erstellter SQL Server -Anmeldename kann eine Datenbank verwenden, wenn der Windows-Benutzer Mitglied einer Windows-Gruppe ist, die auch ein Benutzer der Datenbank ist.

Die Informationen für die Zuordnung eines SQL Server -Anmeldenamens zu einem Datenbankbenutzer werden in der Datenbank gespeichert. Hierzu zählen der Name des Datenbankbenutzers sowie die Sicherheits-ID (SID) des entsprechenden SQL Server -Anmeldenamens. Die Berechtigungen dieses Datenbankbenutzers werden für die Autorisierung in der Datenbank verwendet.

Ein Datenbankbenutzer (basierend auf einem Anmeldenamen), für den ein entsprechender SQL Server -Anmeldename auf einer Serverinstanz nicht oder falsch definiert ist, kann sich bei der Instanz nicht anmelden. Diese Benutzer werden als verwaiste Benutzer der Datenbank dieser Serverinstanz bezeichnet. Verwaisungen treten auf, wenn der Datenbankbenutzer einer Anmelde-SID zugeordnet wird, die auf der master -Instanz nicht vorhanden ist. Ein Datenbankbenutzer kann anschließend zu einem verwaisten Benutzer werden, wenn die Datenbank wiederhergestellt oder an eine andere SQL Server -Instanz angefügt wird, wo der Anmeldename nie erstellt wurde. Ein Datenbankbenutzer kann auch zu einem verwaisten Benutzer werden, wenn der entsprechende SQL Server -Anmeldename gelöscht wird. Selbst wenn der Anmeldename neu erstellt wird, verfügt er über eine andere SID, sodass der Datenbankbenutzer verweist bleibt.

Ermitteln von verwaisten Benutzern

Für SQL Server und PDW

Führen Sie die folgende Anweisung in der Benutzerdatenbank aus, um verwaiste Benutzer in SQL Server über eine Suche nach fehlenden SQL Server -Anmeldenamen zu ermitteln:

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';  

Die Ausgabe führt alle Benutzer der SQL Server -Authentifizierung und entsprechende Sicherheits-IDs (SID), die mit keinem SQL Server -Anmeldenamen verknüpft sind, in der aktuellen Datenbank auf.

Für SQL-Datenbank und Azure Synapse Analytics

Die sys.server_principals-Tabelle ist in der SQL-Datenbank oder in Azure Synapse Analytics nicht verfügbar. Ermitteln Sie verwaiste Benutzer in diesen Umgebungen, indem Sie die folgenden Schritte ausführen:

1. Stellen Sie eine Verbindung mit der master -Datenbank her, und wählen Sie mit der folgenden Abfrage die SIDs für die Anmeldenamen:

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

2. Stellen Sie eine Verbindung mit der Benutzerdatenbank her, und überprüfen Sie mit der folgenden Abfrage die SIDs der Benutzer in der sys.database_principals -Tabelle:

   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. Vergleichen Sie die beiden Listen, um festzustellen, ob die Tabelle sys.database_principals der Benutzerdatenbank SIDs enthält, für die in der Tabelle sql_logins der Masterdatenbank keine entsprechenden Anmelde-SIDs vorhanden sind.

Entfernen eines verwaisten Benutzers

Verwenden Sie in der Masterdatenbank die Anweisung CREATE LOGIN zusammen mit der SID-Option, um einen fehlenden Anmeldenamen neu zu erstellen und geben Sie die SID des Datenbankbenutzers an, die Sie im vorherigen Abschnitt erhalten haben:

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

Führen Sie in der Benutzerdatenbank die Anweisung ALTER USERaus, und geben Sie den Anmeldenamen ein, um verwaiste Benutzer einem Anmeldenamen zuzuordnen, der bereits im Master vorhanden ist.

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

Wenn Sie einen fehlenden Anmeldenamen neu erstellen, kann der Benutzer mithilfe des angegebenen Kennworts auf die Datenbank zugreifen. Der Benutzer kann dann das Kennwort für das Anmeldekonto mit der ALTER LOGIN-Anweisung ändern.

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

Wichtig

Bei jedem Anmeldenamen kann das eigene Kennwort geändert werden. Nur Anmeldenamen mit der Berechtigung ALTER ANY LOGIN können auch die Kennwörter von anderen Benutzern ändern. Allerdings können die Kennwörter von Mitgliedern der sysadmin -Rolle nur von Mitgliedern der sysadmin -Rolle geändert werden.

Weitere Informationen

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)