Dela via

Azure SQL Database-serverroller för behörighetshantering

  • Artikel

Gäller för:Azure SQL Database

I den här artikeln beskrivs fasta roller på servernivå i Azure SQL Database.

Kommentar

De fasta rollerna på servernivå i den här artikeln finns i offentlig förhandsversion för Azure SQL Database. Dessa roller på servernivå ingår också i versionen för SQL Server 2022.

Översikt

I Azure SQL Database är servern ett logiskt begrepp och behörigheter kan inte beviljas på servernivå. För att förenkla behörighetshanteringen tillhandahåller Azure SQL Database en uppsättning fasta roller på servernivå som hjälper dig att hantera behörigheterna på en logisk server. Roller är säkerhetsobjekt som grupperar inloggningar.

Kommentar

Rollkonceptet i den här artikeln är som grupper i Windows-operativsystemet.

Dessa särskilda fasta roller på servernivå använder prefixet ##MS_ och suffixet ## för att skilja från andra vanliga användarskapade huvudkonton.

Precis som lokal SQL Server ordnas serverbehörigheter hierarkiskt. De behörigheter som innehas av dessa roller på servernivå kan spridas till databasbehörigheter. För att behörigheterna ska vara effektivt användbara på databasnivå måste en inloggning antingen vara medlem i rollen ##MS_DatabaseConnector##på servernivå , som beviljar CONNECT till alla databaser eller har ett användarkonto i enskilda databaser. Detta gäller även för den virtuella master databasen.

Rollen ##MS_ServerStateReader## på servernivå innehåller till exempel behörigheten VIEW SERVER STATE. Om en inloggning som är medlem i den här rollen har ett användarkonto i databaserna master och WideWorldImportershar den här användaren behörigheten VIEW DATABASE STATE i dessa två databaser.

Kommentar

Alla behörigheter kan nekas i användardatabaser, vilket i praktiken överskrider det serveromfattande beviljandet via rollmedlemskap. Men i systemdatabasens huvudnamn kan behörigheter inte beviljas eller nekas.

Azure SQL Database tillhandahåller för närvarande sju fasta serverroller. Behörigheter som beviljas till de fasta serverrollerna kan inte ändras och dessa roller kan inte ha andra fasta roller som medlemmar. Du kan lägga till inloggningar på servernivå som medlemmar i roller på servernivå.

Viktigt!

Varje medlem i en fast serverroll kan lägga till andra inloggningar till samma roll.

Mer information om azure SQL Database-inloggningar och -användare finns i Auktorisera databasåtkomst till SQL Database, SQL Managed Instance och Azure Synapse Analytics.

Fasta roller på servernivå

I följande tabell visas de fasta rollerna på servernivå och deras funktioner.

Fast servernivåroll beskrivning
##MS_DatabaseConnector## Medlemmar i den fasta serverrollen ##MS_DatabaseConnector## kan ansluta till valfri databas utan att kräva ett användarkonto i databasen att ansluta till.

Om du vill neka behörigheten CONNECT till en specifik databas kan användarna skapa ett matchande användarkonto för den här inloggningen i databasen och sedan DENY behörigheten CONNECT till databasanvändaren. Den här DENY behörigheten åsidolöser behörigheten GRANT CONNECT som kommer från den här rollen.
##MS_DatabaseManager## Medlemmar i den fasta serverrollen ##MS_DatabaseManager## kan skapa och ta bort databaser. En medlem av rollen ##MS_DatabaseManager## som skapar en databas blir ägare till databasen, vilket gör att användaren kan ansluta till databasen dbo som användare. Användaren dbo har alla databasbehörigheter i databasen. Medlemmar i ##MS_DatabaseManager## rollen har inte nödvändigtvis behörighet att komma åt databaser som de inte äger. Du bör använda den här serverrollen över databasnivårollen dbmanager som finns i master.
##MS_DefinitionReader## Medlemmar i den ##MS_DefinitionReader## fasta serverrollen kan läsa alla katalogvyer som omfattas av VIEW ANY DEFINITION, respektive VIEW DEFINITION i alla databaser där medlemmen i den här rollen har ett användarkonto.
##MS_LoginManager## Medlemmar i den fasta serverrollen ##MS_LoginManager## kan skapa och ta bort inloggningar. Du bör använda den här serverrollen över rollen loginmanager-databasnivå som finns i master.
##MS_SecurityDefinitionReader## Medlemmar i den ##MS_SecurityDefinitionReader## fasta serverrollen kan läsa alla katalogvyer som omfattas av VIEW ANY SECURITY DEFINITIONrespektive har VIEW SECURITY DEFINITION behörighet för alla databaser där medlemmen i den här rollen har ett användarkonto. Det här är en liten delmängd av vad serverrollen ##MS_DefinitionReader## har åtkomst till.
##MS_ServerStateManager## Medlemmar i den ##MS_ServerStateManager## fasta serverrollen har samma behörigheter som ##MS_ServerStateReader## rollen. Dessutom har den behörigheten ALTER SERVER STATE som ger åtkomst till flera hanteringsåtgärder, till exempel: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## Medlemmar i den ##MS_ServerStateReader## fasta serverrollen kan läsa alla dynamiska hanteringsvyer (DMV:er) och funktioner som omfattas av VIEW SERVER STATE, respektive VIEW DATABASE STATE i alla databaser där medlemmen i den här rollen har ett användarkonto.

Behörigheter för fasta serverroller

Varje fast servernivåroll har vissa behörigheter tilldelade till sig. I följande tabell visas de behörigheter som tilldelats till rollerna på servernivå. Den visar också behörigheter på databasnivå som ärvs så länge användaren kan ansluta till enskilda databaser.

Fast servernivåroll Behörigheter på servernivå Behörigheter på databasnivå (om det finns en databasanvändare som matchar inloggningen)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, , VIEW ANY DEFINITIONVIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN Ej tillämpligt
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE

Behörigheter

Endast serveradministratörskontot eller Microsoft Entra-administratörskontot (som kan vara en Microsoft Entra-grupp) kan lägga till eller ta bort andra inloggningar till eller från serverroller. Detta är specifikt för Azure SQL Database.

Kommentar

Microsoft Entra-ID är det nya namnet för Azure Active Directory (Azure AD). Vi uppdaterar dokumentationen just nu.

Arbeta med roller på servernivå

I följande tabell beskrivs systemvyer och funktioner som du kan använda för att arbeta med roller på servernivå i Azure SQL Database.

Funktion Typ Beskrivning
IS_SRVROLEMEMBER Metadata Anger om en SQL-inloggning är medlem i den angivna rollen på servernivå.
sys.server_role_members Metadata Returnerar en rad för varje medlem i varje roll på servernivå.
sys.sql_logins Metadata Returnerar en rad för varje SQL-inloggning.
ÄNDRA SERVERROLL Command Ändrar medlemskapet för en serverroll.

Exempel

Exemplen i det här avsnittet visar hur du arbetar med roller på servernivå i Azure SQL Database.

A. Lägga till en SQL-inloggning till en roll på servernivå

I följande exempel läggs SQL-inloggningen Jiao till rollen ##MS_ServerStateReader##på servernivå . Den här instruktionen måste köras i den virtuella master databasen.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Visa en lista över alla huvudnamn (SQL-autentisering) som är medlemmar i en roll på servernivå

Följande instruktion returnerar alla medlemmar i en fast servernivåroll med hjälp av katalogvyerna sys.server_role_members och sys.sql_logins . Den här instruktionen måste köras i den virtuella master databasen.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Fullständigt exempel: Lägg till en inloggning till en roll på servernivå, hämta metadata för rollmedlemskap och behörigheter och kör en testfråga

Del 1: Förbereda rollmedlemskap och användarkonto

Kör det här kommandot från den virtuella master databasen.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Här är resultatuppsättningen.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Kör det här kommandot från en användardatabas.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Del 2: Testa rollmedlemskap

Logga in som inloggning Jiao och anslut till den användardatabas som används i exemplet.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Kontrollera roller på servernivå för Microsoft Entra-inloggningar

Kör det här kommandot i den virtuella master databasen för att se alla Microsoft Entra-inloggningar som ingår i roller på servernivå i SQL Database. Mer information om Microsoft Entra-serverinloggningar finns i Microsoft Entra-serverhuvudkonton.

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Kontrollera de virtuella master databasrollerna för specifika inloggningar

Kör det här kommandot i den virtuella master databasen för att kontrollera med roller bob har, eller ändra värdet så att det matchar ditt huvudnamn.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Begränsningar för roller på servernivå

  • Rolltilldelningar kan ta upp till 5 minuter att börja gälla. Även för befintliga sessioner börjar ändringar av serverrolltilldelningar inte gälla förrän anslutningen har stängts och öppnats igen. Detta beror på den distribuerade arkitekturen master mellan databasen och andra databaser på samma logiska server.

    • Delvis lösning: för att minska väntetiden och se till att serverrolltilldelningar är aktuella i en databas kan en serveradministratör eller en Microsoft Entra-administratör köras DBCC FLUSHAUTHCACHE i de användardatabaser som inloggningen har åtkomst till. Aktuella inloggade användare måste fortfarande återansluta efter att ha körts DBCC FLUSHAUTHCACHE för att medlemskapsändringarna ska börja gälla för dem.

  • IS_SRVROLEMEMBER() stöds inte i master databasen.

Relaterat innehåll