Dela via

Felsöka överblivna användare (SQL Server)

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Användare överblivs i SQL Server när en databasanvändare baseras på en inloggning i master databasen men inloggningen inte längre finns i master. Detta kan inträffa när inloggningen tas bort eller när databasen flyttas till en annan server där inloggningen inte finns. Den här artikeln beskriver hur du hittar överblivna användare och mappar om dem till inloggningar.

Anmärkning

Minska risken för överblivna användare med hjälp av oberoende databasanvändare för databaser som kan flyttas. Mer information finns i Gör databasen portabel med hjälp av inneslutna databaser.

Bakgrund

För anslutningar till en databas på en instans av SQL Server som använder ett säkerhetsobjekt (databasanvändarens identitet) baserat på en inloggning måste huvudkontot ha en giltig inloggning i master databasen. Den här inloggningen används i autentiseringsprocessen som verifierar huvudkontots identitet och avgör om huvudkontot tillåts ansluta till instansen av SQL Server. SQL Server-inloggningarna på en serverinstans visas i sys.server_principals katalogvyn och kompatibilitetsvyn sys.sql_logins .

SQL Server-inloggningar får åtkomst till enskilda databaser som en "databasanvändare" som mappas till SQL Server-inloggningen. Det finns tre undantag till den här regeln:

  • Inhägnade databasanvändare

    Oberoende databasanvändare autentiserar på användardatabasnivå och är inte associerade med inloggningar. Den här modellen rekommenderas eftersom databaserna är mer portabla och oberoende databasanvändare inte kan bli överblivna. De måste dock återskapas för varje databas. Den här modellen kan vara opraktisk i en miljö som har många databaser.

  • Gästkontot

    När det här kontot är aktiverat i en databas tillåter det att SQL Server-inloggningar som inte mappas till en databasanvändare får åtkomst till databasen som gästanvändare . Gästkontot är inaktiverat som standard.

  • Microsoft Windows-gruppmedlemskap

    En SQL Server-inloggning som skapats från en Windows-användare kan komma åt en databas om Windows-användaren är medlem i en Windows-grupp som också är en användare i databasen.

Information om mappningen av en SQL Server-inloggning till en databasanvändare lagras i databasen. Den innehåller namnet på databasanvändaren och säkerhetsidentifieraren (SID) för motsvarande SQL Server-inloggning. Behörigheterna för den här databasanvändaren tillämpas för auktorisering i databasen.

En databasanvändare (baserat på en inloggning) för vilken motsvarande SQL Server-inloggning är odefinierad eller felaktigt definierad på en serverinstans kan inte logga in på instansen. En sådan användare sägs vara en överbliven användare av databasen på den serverinstansen. Överblivna kan inträffa om databasanvändaren mappas till en inloggning SID som inte finns i master databasen. En databasanvändare kan bli överbliven när en databas har återställts eller kopplats till en annan instans av SQL Server där inloggningen aldrig skapades. En databasanvändare kan också bli överbliven om motsvarande SQL Server-inloggning tas bort. Även om inloggningen återskapas har den en annan SID, så databasanvändaren blir fortfarande överbliven.

Identifiera överblivna användare

För SQL Server och PDW

Om du vill identifiera överblivna användare i SQL Server baserat på saknade SQL Server-autentiseringsinloggningar kör du följande instruktion i användardatabasen:

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

Utdata visar en lista över SQL Server-autentiseringsanvändare och motsvarande SID:er i den aktuella databasen som inte är länkade till någon SQL Server-inloggning.

För Azure SQL Database och Azure Synapse Analytics

Tabellen sys.server_principals är inte tillgänglig i SQL Database eller Azure Synapse Analytics. Identifiera överblivna användare i dessa miljöer genom att utföra följande steg:

  1. Anslut till master databasen och välj SID:erna för inloggningarna med hjälp av följande fråga:

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

  2. Anslut till användardatabasen och granska SID:erna för användarna i sys.database_principals tabellen med hjälp av följande fråga:

    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. Jämför de två listorna för att avgöra om det finns användar-SID:er i användardatabastabellen sys.database_principals som inte matchas av inloggnings-SID:er i databastabellen mastersql_logins .

Lösa en överbliven användare

I databasen master använder du instruktionen CREATE LOGIN (SKAPA INLOGGNING ) med SID alternativet för att återskapa en inloggning som saknas. Ange den SID databasanvändare som du hämtade i föregående avsnitt.

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

Om du vill mappa en överbliven användare till en inloggning som redan finns i masterkör du ALTER USER-instruktionen i användardatabasen och anger inloggningsnamnet:

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

När du återskapar en inloggning som saknas kan användaren komma åt databasen med hjälp av det angivna lösenordet. Användaren kan sedan ändra lösenordet för inloggningskontot med hjälp av -instruktionen ALTER LOGIN :

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

Viktigt!

Alla inloggningar kan ändra sitt eget lösenord. Endast inloggningar med behörigheten ALTER ANY LOGIN kan ändra lösenordet för en annan användares inloggning. Det är dock bara medlemmar i sysadmin-rollen som kan ändra lösenord för sysadmin-rollmedlemmar .

Relaterat innehåll