Het Beheren van Weesgebruikers in SQL Server: Een Uitgebreide Gids

Het beheren van weesgebruikers in SQL Server is essentieel voor de beveiliging en het beheer van databases. Weesgebruikers ontstaan wanneer een databasegebruiker bestaat zonder een overeenkomstige login in de masterdatabase. Deze ongelijkheid kan de toegang verstoren en leiden tot mogelijke beveiligingskwetsbaarheden. In dit artikel zullen we verkennen wat weesgebruikers zijn, hoe ze te detecteren en methoden om ze op te lossen, inclusief bijgewerkte voorbeelden en scenario’s.

Achtergrond

Om verbinding te maken met een SQL Server-database, moet een gebruiker een geldige login hebben in de masterdatabase. Deze login verifieert de gebruiker en wordt gekoppeld aan een databasegebruiker voor autorisatie. De koppeling wordt onderhouden met behulp van de Security Identifier (SID), waardoor databasegebruikers en serverlogins correct zijn gekoppeld.

Uitzonderingen op de regel van login-gebruikerskoppeling zijn onder andere:

1. Op zichzelf staande databasegebruikers: Authenticeren op het databaseniveau, waardoor de afhankelijkheid van serverlogins wordt geëlimineerd. Deze gebruikers verbeteren de draagbaarheid van de database, maar vereisen aparte recreatie voor elke database.
2. Gastaccounts: Standaard ingeschakeld, ze staan toegang toe aan gebruikers zonder expliciete koppeling maar worden over het algemeen afgeraden om beveiligingsredenen.
3. Lidmaatschappen van Microsoft Windows-groepen: Windows-groepleden kunnen databases benaderen als de groep als gebruikers is toegevoegd.

Een databasegebruiker wordt wees wanneer:

• De overeenkomstige login is verwijderd.
• De database wordt gemigreerd of hersteld zonder de bijbehorende logins.
• De SID-mapping tussen de gebruiker en de login is onjuist.

Wat zijn weesgebruikers?

Een weesgebruiker ontstaat wanneer:

• De databasegebruiker bestaat, maar de bijbehorende SQL Server-login in de masterdatabase niet.
• De databasegebruiker SID en login SID komen niet overeen.

Deze scenario’s verstoren de authenticatie en autorisatie, wat leidt tot fouten of beperkte toegang.

Weesgebruikers detecteren

Voor SQL Server

Gebruik de volgende query om weesgebruikers te identificeren:

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

Voor Azure SQL Database of Synapse Analytics

1. Haal de SIDs van logins in de masterdatabase op:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Haal de SIDs van gebruikers in de doeldatabase op:

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. Vergelijk de lijsten om mismatches te identificeren.

Weesgebruikers oplossen

1. Maak de ontbrekende login opnieuw aan met SID

Als de login is verwijderd:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Koppel een weesgebruiker aan een bestaande login

Als de login bestaat maar niet is gekoppeld:

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

3. Wijzig het wachtwoord van de gebruiker

Na het opnieuw aanmaken van de login, werk het wachtwoord bij:

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

Gebruik sp_FindOrphanedUser

De opgeslagen procedure sp_FindOrphanedUser is een krachtig hulpmiddel voor het detecteren en oplossen van weesgebruikers. Het biedt:

1. Een lijst van weesgebruikers.
2. Automatisch gegenereerde scripts voor reparaties.
3. Details over schema’s die eigendom zijn van weesgebruikers.

Haal de pakketcode op van sp_FindOrphanedUser.

Uitvoeren van de Procedure

Standaard uitvoering:

EXEC sp_FindOrphanedUser;

Voor een specifieke database:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Voorbeelden van Scenario’s met Weesgebruikers

Voorbeeld 1: Gebruiker Zonder Login

Scenario

Een login is verwijderd, waardoor de databasegebruiker wees wordt.

• Detecteer de wees gebruiker:
  MS SQL

  EXEC sp_FindOrphanedUser;

• Recreëer de aanmelding:
  MS SQL

   

  CREËER AANMELDING [User1] MET WACHTWOORD = 'SecurePassword123';

  GEBRUIK [ExampleDB];

  WIJZIG GEBRUIKER [User1] MET AANMELDING = [User1];

Voorbeeld 2: SID Mismatch

Scenario

De inlog-SID komt niet overeen met de gebruikers-SID.

• Detecteer de ongelijkheid:
  MS SQL

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Corrigeer de SID-mapping:
  MS SQL

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

Voorbeeld 3: Weesgebruiker die een schema bezit

Scenario

Weesgebruiker verhindert de verwijdering van het schema.

• Schema-eigendom detecteren:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Wijs schema-eigendom opnieuw toe:
  MS SQL

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

Beste Praktijken

• Gebruik Gebruikers met Beperkte Toegang tot de Database: Vermijd afhankelijkheid van serverlogins.
• Synchroniseer Logins: Migreer of herstel altijd logins tijdens database migraties.
• Regelmatige Audits: Controleer periodiek op weesgebruikers om de beveiliging te handhaven.

Conclusie

Het beheren van weesgebruikers zorgt voor databasebeveiliging en operationele efficiëntie. Door het gebruik van queries en tools zoals sp_FindOrphanedUser kunt u snel problemen identificeren en oplossen, waardoor legitieme gebruikers naadloze toegang behouden. Het aannemen van beste praktijken zoals beperkte gebruikers en regelmatige audits kan dergelijke problemen in de toekomst voorkomen.