Fastställa gällande behörigheter för databasmotorn

Artikel
2025-03-05

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL-databas i Microsoft Fabric

Den här artikeln beskriver hur du avgör vem som har behörighet till olika objekt i SQL Server Database Engine. SQL Server implementerar två behörighetssystem för databasmotorn. Ett äldre system med fasta roller har förkonfigurerade behörigheter. Från och med SQL Server 2005 (9.x) finns ett mer flexibelt och exakt system tillgängligt.

Not

Informationen i den här artikeln gäller för SQL Server 2005 (9.x) och senare versioner. Vissa typer av behörigheter är inte tillgängliga i vissa versioner av SQL Server.

Du bör alltid tänka på följande:

De effektiva behörigheterna är aggregerade för båda behörighetssystemen.
Ett nekande av behörigheter åsidosätter beviljande av behörigheter.
Om en användare är medlem i den fasta serverrollen sysadmin kontrolleras inte behörigheterna ytterligare, så avslag tillämpas inte.
Det gamla systemet och det nya systemet har likheter. Medlemskap i den sysadmin fasta serverrollen liknar till exempel att ha CONTROL SERVER behörighet. Men systemen är inte identiska. Om en inloggning till exempel bara har behörigheten CONTROL SERVER, och en lagrad procedur kontrollerar medlemskap i den fasta serverrollen sysadmin, så kommer behörighetskontrollen att misslyckas. Det omvända är också sant.
I Fabric SQL Database är Microsoft Entra-ID för databasanvändare den enda autentiseringsmetod som stöds. Roller och behörigheter på servernivå är inte tillgängliga, bara på databasnivå. Mer information finns i Behörighet i SQL-databasen i Microsoft Fabric.

Sammanfattning

Behörighet på servernivå kan komma från medlemskap i fasta serverroller eller användardefinierade serverroller. Alla tillhör den public fasta serverrollen och får alla behörigheter som tilldelats där.
Behörigheter på servernivå kan komma från behörighetstilldelningar till inloggningar eller användarspecifika serverroller.
Behörighet på databasnivå kan komma från medlemskap i fasta databasroller eller användardefinierade databasroller i varje databas. Alla tillhör den public fasta databasrollen och får alla behörigheter som tilldelats där.
Behörigheter på databasnivå kan komma från behörighetsbidrag till användare eller användardefinierade databasroller i varje databas.
Behörigheter kan mottas från guest inloggning eller guest databasanvändare om det är aktiverat. Den guest inloggningen och användarkontona är inaktiverade som standard.
Windows-användare kan vara medlemmar i Windows-grupper som kan ha inloggningar. SQL Server lär sig om Windows-gruppmedlemskap när en Windows-användare ansluter och presenterar en Windows-token med säkerhetsidentifieraren för en Windows-grupp. Eftersom SQL Server inte hanterar eller tar emot automatiska uppdateringar om Windows-gruppmedlemskap kan SQL Server inte på ett tillförlitligt sätt rapportera behörigheterna för Windows-användare som tas emot från Windows-gruppmedlemskap.
Behörigheter kan hämtas genom att växla till en programroll och ange lösenordet.
Behörigheter kan hämtas genom att köra en lagrad procedur som innehåller EXECUTE AS-satsen.
Behörigheter kan fås av inloggningar eller användare med behörigheten IMPERSONATE.
Medlemmar i den lokala datoradministratörsgruppen kan alltid höja sina privilegier till att sysadmin. (Gäller inte för SQL Database.)
Medlemmar i securityadmin fast serverroll kan höja många av sina privilegier och kan i vissa fall höja behörigheterna till sysadmin. (Gäller inte för SQL Database.)
SQL Server-administratörer kan se information om alla inloggningar och användare. Mindre privilegierade användare ser vanligtvis bara information om sina egna identiteter.

Äldre system för fast rollbehörighet

Serverroller och fasta databasroller har förkonfigurerade behörigheter som inte kan ändras. Kör följande fråga för att avgöra vem som är medlem i en fast serverroll:

Notera

Gäller inte för SQL Database eller Azure Synapse Analytics där behörighet på servernivå inte är tillgänglig. Kolumnen is_fixed_role i sys.server_principals lades till i SQL Server 2012 (11.x). Det behövs inte för äldre versioner av SQL Server.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Anteckning

Alla inloggningar är medlemmar i den offentliga rollen och kan inte tas bort. Frågan kontrollerar tabellerna i den master databasen, men den kan köras i valfri databas för den lokala produkten.

Kör följande fråga i varje databas för att avgöra vem som är medlem i en fast databasroll.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Information om vilka behörigheter som beviljas för varje roll finns i rollbeskrivningarna på illustrationer i Books Online (roller på servernivåoch roller på databasnivå).

Nyare detaljerad behörighetssystem

Det här systemet är flexibelt, vilket innebär att det kan vara komplicerat om de personer som konfigurerar det vill vara exakta. För att förenkla saker och ting hjälper det till att skapa roller, tilldela behörigheter till roller och sedan lägga till grupper av personer i rollerna. Och det är enklare om databasutvecklingsteamet separerar aktivitet efter schema och sedan ger rollbehörighet till ett helt schema i stället för till enskilda tabeller eller procedurer. Verkliga scenarier är komplexa och affärsbehov kan skapa oväntade säkerhetskrav.

Följande bild visar behörigheter och deras relationer till varandra. Vissa behörigheter på högre nivå (till exempel CONTROL SERVER) visas många gånger. I den här artikeln är affischen alldeles för liten för att läsa. Du kan ladda ned den fullstora postern över behörigheter för databasmotorn i PDF-format.

En skärmbild från PDF-behörigheterna för databasmotorn.

Säkerhetsklasser

Behörigheter kan beviljas på servernivå, databasnivå, schemanivå eller objektnivå osv. Det finns 26 nivåer (kallas klasser). Den fullständiga listan över klasser i alfabetisk ordning är: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Vissa klasser är inte tillgängliga på vissa typer av SQL Server.) För att ge fullständig information om varje klass krävs en annan fråga.

Huvudmän

Behörigheter beviljas till huvudansvariga. Principaler kan vara serverroller, inloggningar, databasroller eller användare. Inloggningar kan representera Windows-grupper som innehåller många Windows-användare. Eftersom Windows-grupper inte underhålls av SQL Server vet SQL Server inte alltid vem som är medlem i en Windows-grupp. När en Windows-användare ansluter till SQL Server innehåller inloggningspaketet windows-gruppmedlemskapstoken för användaren.

När en Windows-användare ansluter med en inloggning baserad på en Windows-grupp kan vissa aktiviteter kräva att SQL Server skapar en inloggning eller användare för att representera den enskilda Windows-användaren. En Windows-grupp (tekniker) innehåller till exempel användare (Mary, Todd, Pat) och gruppen Tekniker har ett databasanvändarkonto. Om Mary har behörighet och skapar en tabell kan en användare (Mary) skapas som ägare till tabellen. Eller om Todd nekas en behörighet som resten av gruppen Tekniker har, måste användaren Todd skapas för att spåra behörighetsnekelsen.

Kom ihåg att en Windows-användare kan vara medlem i mer än en Windows-grupp (till exempel både tekniker och chefer). Behörigheter som beviljats eller nekats teknikers inloggning, chefers inloggning, användaren individuellt och roller som användaren är medlem i, kommer alla att sammanställas och utvärderas för att fastställa de gällande behörigheterna. Funktionen HAS_PERMS_BY_NAME kan visa om en användare eller inloggning har en viss behörighet. Det finns dock inget uppenbart sätt att fastställa källan till beviljandet eller nekande av tillstånd. Studera listan över behörigheter och experimentera kanske genom försök och misstag.

Användbara frågor

Serverbehörigheter

Följande fråga returnerar en lista över de behörigheter som har beviljats eller nekats på servernivå. Den här frågan ska köras i master-databasen.

Notera

Behörigheter på servernivå kan inte beviljas eller efterfrågas i SQL Database eller Azure Synapse Analytics.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Databasbehörigheter

Följande fråga returnerar en lista över de behörigheter som har beviljats eller nekats på databasnivå. Den här frågan ska köras i varje databas.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

Varje behörighetsklass i behörighetstabellen kan kopplas till andra systemvyer som ger relaterad information om den klassen av säkerhetsobjekt. Följande fråga innehåller till exempel namnet på databasobjektet som påverkas av behörigheten.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

Använd funktionen HAS_PERMS_BY_NAME för att avgöra om en viss användare (i det här fallet TestUser) har en behörighet. Till exempel:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Mer information om syntaxen finns i HAS_PERMS_BY_NAME.

Dela via