Verwaiste Benutzer in SQL Server verwalten: Ein umfassender Leitfaden

Die Verwaltung von verwaisten Benutzern in SQL Server ist entscheidend für die Sicherheit und Verwaltung von Datenbanken. Verwaiste Benutzer entstehen, wenn ein Datenbankbenutzer existiert, ohne dass ein entsprechendes Login in der Master-Datenbank vorhanden ist. Diese Diskrepanz kann den Zugriff stören und zu potenziellen Sicherheitsanfälligkeiten führen. In diesem Artikel werden wir untersuchen, was verwaiste Benutzer sind, wie man sie erkennt und Methoden zu ihrer Behebung, einschließlich aktualisierter Beispiele und Szenarien.

Hintergrund

Um eine Verbindung zu einer SQL Server-Datenbank herzustellen, muss ein Benutzer über ein gültiges Login in der Master-Datenbank verfügen. Dieses Login authentifiziert den Benutzer und wird einem Datenbankbenutzer für die Autorisierung zugeordnet. Die Zuordnung wird mithilfe des Security Identifiers (SID) aufrechterhalten, um sicherzustellen, dass Datenbankbenutzer und Server-Logins korrekt verknüpft sind.

Ausnahmen von der Regel der Login-Benutzer-Zuordnung sind:

1. Enthaltene Datenbankbenutzer: Authentifizieren auf Datenbankebene und beseitigen die Abhängigkeit von Server-Logins. Diese Benutzer verbessern die Portabilität der Datenbank, erfordern jedoch eine separate Neuerstellung für jede Datenbank.
2. Gastkonten: Standardmäßig aktiviert, ermöglichen sie den Zugriff für Benutzer ohne explizite Zuordnung, werden jedoch aus Sicherheitsgründen allgemein discouraged.
3. Microsoft Windows-Gruppenmitgliedschaften: Windows-Gruppenmitglieder können auf Datenbanken zugreifen, wenn die Gruppe als Benutzer hinzugefügt wird.

Ein Datenbankbenutzer wird verwaist, wenn:

• Das entsprechende Login gelöscht wird.
• Die Datenbank migriert oder wiederhergestellt wird, ohne dass die zugehörigen Logins vorhanden sind.
• Die SID-Zuordnung zwischen Benutzer und Anmeldung ist falsch.

Was sind verwaiste Benutzer?

Ein verwaister Benutzer entsteht, wenn:

• Der Datenbankbenutzer existiert, aber die entsprechende SQL Server-Anmeldung in der Masterdatenbank nicht.
• Die SID des Datenbankbenutzers und die SID der Anmeldung nicht übereinstimmen.

Diese Szenarien stören die Authentifizierung und Autorisierung und führen zu Fehlern oder eingeschränktem Zugriff.

Ermitteln verwaister Benutzer

Für SQL Server

Verwenden Sie die folgende Abfrage, um verwaiste Benutzer zu identifizieren:

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

Für Azure SQL-Datenbank oder Synapse Analytics

1. Rufen Sie die SIDs der Anmeldungen in der Masterdatenbank ab:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Rufen Sie die SIDs der Benutzer in der Zieldatenbank ab:

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 Listen, um Unstimmigkeiten zu identifizieren.

Beheben verwaister Benutzer

1. Fehlende Anmeldung mit SID neu erstellen 

Wenn die Anmeldung gelöscht wurde:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Verwaisten Benutzer einer bestehenden Anmeldung zuordnen 

Wenn die Anmeldung existiert, aber nicht zugeordnet ist:

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

3. Benutzerkennwort ändern 

Aktualisieren Sie nach dem Neuerstellen der Anmeldung das Kennwort:

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

Verwenden von sp_FindOrphanedUser

Die gespeicherte Prozedur `sp_FindOrphanedUser` ist ein leistungsstarkes Tool zum Erkennen und Beheben verwaister Benutzer. Sie bietet:

1. Eine Liste verwaister Benutzer.
2. Automatisch generierte Skripts für Reparaturen.
3. Details zu Schemas im Besitz verwaister Benutzer.

Holen Sie sich den Paketcode von sp_FindOrphanedUser.

Prozedurausführung

Standardausführung:

EXEC sp_FindOrphanedUser;

Für eine bestimmte Datenbank:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Beispiele für Szenarien mit verwaisten Benutzern

Beispiel 1: Benutzer ohne Anmeldung

Szenario

Eine Anmeldung wird gelöscht, wodurch der Datenbankbenutzer verwaist wird.

• Erkennen Sie den verwaisten Benutzer:
  MS SQL

   

  EXEC sp_FindOrphanedUser;

• MS SQL

   

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

  USE [ExampleDB];

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

Beispiel 2: SID-Fehler

Szenario

Die Anmelde-SID stimmt nicht mit der Benutzer-SID überein.

• Erkennen Sie die Unstimmigkeit:
  MS SQL

   

  EXEC sp_FindOrphanedUser @Datenbankname = 'BeispielDB';

• Korrigieren Sie die SID-Zuordnung:
  MS SQL

  VERWENDEN [BeispielDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'Benutzer2', 'Benutzer2';

Beispiel 3: Verwaister Benutzer besitzt ein Schema

Szenario

Verwaister Benutzer verhindert das Löschen des Schemas.

• Schema-Eigentum erkennen:
  MS SQL

   

  EXEC sp_FindOrphanedUser @Datenbankname = 'BeispielDB';

• Neuzuweisung des Schemaeigentums:
  MS SQL

  USE [BeispielDB];

  ÄNDERN BERECHTIGUNG AUF SCHEMA::[Benutzer3Schema] ZU [dbo];

  LÖSCHEN BENUTZER [Benutzer3];

Best Practices

• Verwendung von eingeschränkten Datenbankbenutzern: Vermeiden Sie Abhängigkeiten von Serveranmeldungen.
• Synchronisierung von Anmeldungen: Migrieren oder stellen Sie Anmeldungen immer während Datenbankmigrationen wieder her.
• Regelmäßige Audits: Überprüfen Sie regelmäßig auf verwaiste Benutzer, um die Sicherheit zu gewährleisten.

Fazit

Die Verwaltung verwaister Benutzer gewährleistet die Sicherheit und betriebliche Effizienz der Datenbank. Durch die Verwendung von Abfragen und Tools wie sp_FindOrphanedUser können Sie Probleme schnell identifizieren und beheben und einen nahtlosen Zugriff für berechtigte Benutzer gewährleisten. Die Übernahme bewährter Verfahren wie eingeschränkter Benutzer und regelmäßiger Audits kann solche Probleme in Zukunft verhindern.