Delen via

Problemen met zwevende gebruikers oplossen (SQL Server)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Gebruikers zijn zwevend in SQL Server wanneer een databasegebruiker is gebaseerd op een aanmelding in de master database, maar de aanmelding bestaat niet meer in master. Dit kan gebeuren wanneer de aanmelding wordt verwijderd of wanneer de database wordt verplaatst naar een andere server waarop de aanmelding niet bestaat. In dit artikel wordt beschreven hoe u zwevende gebruikers kunt vinden en ze opnieuw kunt toewijzen aan aanmeldingen.

Opmerking

Verminder de mogelijkheid van zwevende gebruikers door ingesloten databasegebruikers te gebruiken voor databases die mogelijk worden verplaatst. Zie Uw database draagbaar maken met behulp van ingesloten databasesvoor meer informatie.

Achtergrond

Voor verbindingen met een database op een exemplaar van SQL Server die een beveiligingsprincipal (databasegebruikersidentiteit) gebruiken op basis van een aanmelding, moet de principal een geldige aanmelding in de master database hebben. Deze aanmelding wordt gebruikt in het verificatieproces waarmee de identiteit van de principal wordt geverifieerd en wordt bepaald of de principal verbinding mag maken met het exemplaar van SQL Server. De SQL Server-aanmeldingen op een serverexemplaren zijn zichtbaar in de sys.server_principals catalogusweergave en de compatibiliteitsweergave sys.sql_logins .

SQL Server-aanmeldingen hebben toegang tot afzonderlijke databases als een databasegebruiker die is toegewezen aan de SQL Server-aanmelding. Er zijn drie uitzonderingen op deze regel:

  • Ingesloten databasegebruikers

    Ingesloten databasegebruikers verifiëren zich op gebruikersdatabaseniveau en zijn niet gekoppeld aan aanmeldingen. Dit model wordt aanbevolen omdat de databases draagbaarder zijn en ingesloten databasegebruikers niet zwevend kunnen worden. Ze moeten echter opnieuw worden gemaakt voor elke database. Dit model kan onpraktisch zijn in een omgeving met veel databases.

  • Het gastaccount

    Wanneer dit account is ingeschakeld in een database, kunnen SQL Server-aanmeldingen die niet zijn toegewezen aan een databasegebruiker, toegang krijgen tot de database als gastgebruiker . Het gastaccount is standaard uitgeschakeld.

  • Microsoft Windows-groepslidmaatschappen

    Een SQL Server-aanmelding die is gemaakt op basis van een Windows-gebruiker, heeft toegang tot een database als de Windows-gebruiker lid is van een Windows-groep die ook een gebruiker in de database is.

Informatie over de toewijzing van een SQL Server-aanmelding aan een databasegebruiker wordt opgeslagen in de database. Deze bevat de naam van de databasegebruiker en de beveiligings-id (SID) van de bijbehorende SQL Server-aanmelding. De machtigingen van deze databasegebruiker worden toegepast op autorisatie in de database.

Een databasegebruiker (op basis van een aanmelding) waarvoor de bijbehorende SQL Server-aanmelding niet is gedefinieerd of onjuist is gedefinieerd op een serverexemplaren, kan zich niet aanmelden bij het exemplaar. Een dergelijke gebruiker wordt geacht een zwevende gebruiker van de database op dat serverexemplaren te zijn. Zwevend kan optreden als de databasegebruiker is toegewezen aan een aanmelding SID die niet aanwezig is in de master database. Een databasegebruiker kan zwevend raken nadat een database is hersteld of gekoppeld aan een ander exemplaar van SQL Server, waar de aanmelding nooit is gemaakt. Een databasegebruiker kan ook zwevend worden als de bijbehorende SQL Server-aanmelding wordt verwijderd. Zelfs als de aanmelding opnieuw wordt gemaakt, heeft deze een andere SID, zodat de databasegebruiker nog steeds zwevend is.

Zwevende gebruikers detecteren

Voor SQL Server en PDW

Als u zwevende gebruikers in SQL Server wilt detecteren op basis van ontbrekende aanmeldingen voor SQL Server-verificatie, voert u de volgende instructie uit in de gebruikersdatabase:

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

De uitvoer bevat de SQL Server-verificatiegebruikers en bijbehorende SID's in de huidige database die niet zijn gekoppeld aan een SQL Server-aanmelding.

Voor Azure SQL Database en Azure Synapse Analytics

De sys.server_principals tabel is niet beschikbaar in SQL Database of Azure Synapse Analytics. Identificeer zwevende gebruikers in deze omgevingen door de volgende stappen uit te voeren:

  1. Maak verbinding met de master database en selecteer de SID's voor de aanmeldingen met behulp van de volgende query:

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

  2. Maak verbinding met de gebruikersdatabase en controleer de SID's van de gebruikers in de sys.database_principals tabel met behulp van de volgende query:

    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 twee lijsten om te bepalen of er gebruikers-SID's in de gebruikersdatabasetabel sys.database_principals staan die niet overeenkomen met aanmeldings-SID's in de master databasetabel sql_logins .

Een zwevende gebruiker oplossen

Gebruik in de master database de instructie CREATE LOGIN met de SID optie om een ontbrekende aanmelding opnieuw te maken. Geef de SID databasegebruiker op die u in de vorige sectie hebt verkregen.

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

Als u een zwevende gebruiker wilt toewijzen aan een aanmelding die al bestaat master, voert u de instructie ALTER USER uit in de gebruikersdatabase en geeft u de aanmeldingsnaam op:

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

Wanneer u een ontbrekende aanmelding opnieuw maakt, heeft de gebruiker toegang tot de database met behulp van het opgegeven wachtwoord. De gebruiker kan vervolgens het wachtwoord van het aanmeldingsaccount wijzigen met behulp van de ALTER LOGIN instructie:

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

Belangrijk

Elke aanmelding kan een eigen wachtwoord wijzigen. Alleen aanmeldingen met de ALTER ANY LOGIN machtiging kunnen het wachtwoord van de aanmelding van een andere gebruiker wijzigen. Alleen leden van de rol sysadmin kunnen echter wachtwoorden van sysadmin-rolleden wijzigen.

Verwante inhoud