Azure SQL-Datenbank-Serverrollen für die Berechtigungsverwaltung

  • Artikel

Gilt für:Azure SQL-Datenbank

In diesem Artikel werden feste Rollen auf Serverebene in Azure SQL-Datenbank beschrieben.

Hinweis

Die festen Rollen auf Serverebene in diesem Artikel befinden sich in der öffentlichen Vorschauversion für Azure SQL-Datenbank. Diese Rollen auf Serverebene sind ebenfalls Teil des Releases für SQL Server 2022.

Übersicht

In Azure SQL-Datenbank ist der Server ein logisches Konzept, und auf Serverebene können keine Berechtigungen gewährt werden. Zur Vereinfachung der Berechtigungsverwaltung verfügt Azure SQL-Datenbank über eine Gruppe fester Rollen auf Serverebene, die Sie zum Verwalten der Berechtigungen auf einem logischen Server verwenden können. Bei Rollen handelt es sich um Sicherheitsprinzipale, unter denen Anmeldenamen gruppiert sind.

Hinweis

In diesem Artikel können Rollen wie Gruppen im Windows-Betriebssystem angesehen werden.

Für diese speziellen festen Rollen auf Serverebene werden das Präfix ##MS_ und das Suffix ## genutzt, um eine Unterscheidung gegenüber anderen regulären Prinzipalen zu ermöglichen, die von Benutzern erstellt werden.

Serverberechtigungen sind hierarchisch strukturiert, wie dies auch bei einer lokalen SQL Server-Instanz der Fall ist. Die Berechtigungen, über die diese Rollen auf Serverebene verfügen, können auf Datenbankberechtigungen übertragen werden. Damit die Berechtigungen auf Datenbankebene effektiv genutzt werden können, muss eine Anmeldung entweder Mitglied der ##MS_DatabaseConnector##-Rolle auf Serverebene sein, die die Berechtigung CONNECT für alle Datenbanken gewährt, oder über ein Benutzerkonto in den einzelnen Datenbanken verfügen. Dies gilt auch für die virtuelle master-Datenbank.

Beispielsweise enthält die Rolle ##MS_ServerStateReader## auf Serverebene die Berechtigung VIEW SERVER STATE. Wenn für einen Anmeldenamen, der Mitglied dieser Rolle ist, ein Benutzerkonto in den Datenbanken master und WideWorldImporters vorhanden ist, verfügt dieser Benutzer in diesen beiden Datenbanken über die Berechtigung VIEW DATABASE STATE.

Hinweis

Da in Benutzerdatenbanken jede Berechtigung verweigert werden kann, wird die serverweite Gewährung per Rollenmitgliedschaft quasi außer Kraft gesetzt. In der Systemdatenbank master können Berechtigungen aber nicht gewährt oder verweigert werden.

Azure SQL-Datenbank verfügt derzeit über sieben feste Serverrollen. Die Berechtigungen, die den festen Serverrollen gewährt werden, können nicht geändert werden, und diese Rollen können keine anderen festen Rollen als Mitglieder haben. Sie können Anmeldenamen auf Serverebene den Rollen auf Serverebene als Mitglieder hinzufügen.

Wichtig

Jedes Mitglied einer festen Serverrolle kann der gleichen Rolle andere Anmeldenamen hinzufügen.

Weitere Informationen zu Azure SQL-Datenbank-Anmeldenamen und -Benutzern finden Sie unter Autorisieren des Datenbankzugriffs für Azure SQL-Datenbank, SQL Managed Instance und Azure Synapse Analytics.

Feste Rollen auf Serverebene

In der folgenden Tabelle werden die festen Rollen auf Serverebene und deren Möglichkeiten angezeigt.

Feste Rolle auf Serverebene Beschreibung
##MS_DatabaseConnector## Mitglieder der festen Serverrolle ##MS_DatabaseConnector## können sich mit jeder Datenbank verbinden, ohne dass dafür ein Benutzerkonto in der Datenbank erforderlich ist.

Um die CONNECT-Berechtigung für eine bestimmte Datenbank zu verweigern, können Benutzer*innen ein entsprechendes Benutzerkonto für diese Anmeldung in der Datenbank erstellen und dann die DENY-Berechtigung für den*die Datenbankbenutzer*in mit CONNECT überschreiben. Diese DENY-Berechtigung hebt die GRANT CONNECT-Berechtigung dieser Rolle auf.
##MS_DatabaseManager## Mitglieder der festen Serverrolle ##MS_DatabaseManager## können Datenbanken erstellen und löschen. Ein Mitglied der Rolle ##MS_DatabaseManager##, das eine Datenbank erstellt, wird zum Besitzer bzw. der Besitzerin der betreffenden Datenbank. Diese*r Benutzer*in kann dann als dbo-Benutzer*in eine Verbindung mit der Datenbank herstellen. Der*Die dbo-Benutzer*in verfügt über alle Datenbankberechtigungen in der Datenbank. Mitglieder der Rolle ##MS_DatabaseManager## verfügen nicht zwangsweise über die Berechtigung für den Zugriff auf Datenbanken, die sie nicht besitzen. Sie sollten diese Serverrolle anstelle der dbmanager-Rolle auf Datenbankebene verwenden, die in master vorhanden ist.
##MS_DefinitionReader## Mitglieder der festen Serverrolle ##MS_DefinitionReader## können alle Katalogsichten lesen, die von VIEW ANY DEFINITION abgedeckt werden, bzw. von VIEW DEFINITION für alle Datenbanken, für die das Mitglied dieser Rolle über ein Benutzerkonto verfügt.
##MS_LoginManager## Mitglieder der festen Serverrolle ##MS_LoginManager## können Anmeldungen erstellen oder löschen. Sie sollten diese Serverrolle anstelle der loginmanager-Rolle auf Datenbankebene zu verwenden, die in master vorhanden ist.
##MS_SecurityDefinitionReader## Mitglieder der festen Serverrolle ##MS_SecurityDefinitionReader## können alle Katalogsichten lesen, die von VIEW ANY SECURITY DEFINITION abgedeckt werden, und haben die Berechtigung VIEW SECURITY DEFINITION auf jeder Datenbank, auf der das Mitglied dieser Rolle ein Benutzerkonto hat. Dies ist eine kleine Teilmenge dessen, worauf die Serverrolle ##MS_DefinitionReader## Zugriff hat.
##MS_ServerStateManager## Mitglieder der festen Serverrolle ##MS_ServerStateManager## verfügen über die gleichen Berechtigungen, die für die Rolle ##MS_ServerStateReader## gelten. Darüber hinaus ist auch die Berechtigung ALTER SERVER STATE vorhanden, die den Zugriff auf mehrere Verwaltungsvorgänge ermöglicht, z. B. DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL') und DBCC SQLPERF();
##MS_ServerStateReader## Mitglieder der festen Serverrolle ##MS_ServerStateReader## können alle dynamischen Verwaltungssichten (Dynamic Management Views, DMVs) und Funktionen lesen, die von VIEW SERVER STATE abgedeckt werden, bzw. von VIEW DATABASE STATE für alle Datenbanken, für die das Mitglied dieser Rolle über ein Benutzerkonto verfügt.

Berechtigungen von festen Serverrollen

Jede feste Rolle auf Serverebene besitzt bestimmte Berechtigungen. In der folgenden Tabelle sind die Berechtigungen angegeben, die den Rollen auf Serverebene zugewiesen sind. Außerdem werden Berechtigungen auf Datenbankebene angezeigt, die geerbt werden, solange der Benutzer eine Verbindung mit einzelnen Datenbanken herstellen kann.

Feste Rolle auf Serverebene Berechtigungen auf Serverebene Berechtigungen auf Datenbankebene (wenn ein dem Login entsprechender Datenbankbenutzer existiert)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN N/V
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE

Berechtigungen

Nur das Server-Administratorkonto oder das Microsoft Entra-Administratorkonto (welches eine Microsoft Entra-Gruppe sein kann) kann andere Anmeldungen zu oder von Serverrollen hinzufügen oder entfernen. Dies gilt nur für Azure SQL-Datenbank.

Hinweis

Microsoft Entra ID war zuvor als Azure Active Directory (Azure AD) bekannt.

Verwenden Sie Rollen auf Serverebene

In der folgenden Tabelle sind die Systemsichten und Funktionen aufgeführt, die Sie beim Verwenden von Rollen auf Serverebene in Azure SQL-Datenbank nutzen können.

Funktion type Beschreibung
IS_SRVROLEMEMBER Metadaten Gibt an, ob ein SQL-Anmeldename Mitglied der angegebenen Rolle auf Serverebene ist.
sys.server_role_members Metadaten Gibt eine Zeile für jedes Mitglied jeder Rolle auf Serverebene zurück.
sys.sql_logins Metadaten Gibt eine Zeile für jeden SQL-Anmeldenamen zurück.
ALTER SERVER ROLE Get-Help Ändert die Mitgliedschaft einer Serverrolle.

Beispiele

Anhand der Beispiele in diesem Abschnitt wird veranschaulicht, wie Sie Rollen auf Serverebene in Azure SQL-Datenbank verwenden.

A. Fügen Sie einen SQL-Anmeldenamen zu einer Rolle auf Serverebene hinzu

Im folgenden Beispiel wird die SQL-Anmeldung Jiao der Serverrolle ##MS_ServerStateReader## hinzugefügt. Diese Anweisung muss in der virtuellen master-Datenbank ausgeführt werden.

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

B. Listen Sie alle Prinzipale (SQL-Authentifizierung) auf, die Mitglieder einer Rolle auf Serverebene sind

Mit der folgenden Anweisung werden alle Mitglieder aller festen Rollen auf Serverebene zurückgegeben, indem die Katalogsichten sys.server_role_members und sys.sql_logins verwendet werden. Diese Anweisung muss in der virtuellen master-Datenbank ausgeführt werden.

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. Vollständiges Beispiel: Fügen Sie einen Anmeldenamen zu einer Rolle auf Serverebene, rufen Sie Metadaten für die Rollenmitgliedschaft und Berechtigungen ab und Führen Sie eine Testabfrage aus

Teil 1: Vorbereiten der Rollenmitgliedschaft und des Benutzerkontos

Führen Sie diesen Befehl in der virtuellen master-Datenbank aus.

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

Hier ist das Resultset.

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

Führen Sie diesen Befehl aus einer Benutzerdatenbank aus.

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

Teil 2: Testen der Rollenmitgliedschaft

Melden Sie sich mit dem Anmeldenamen Jiao an, und stellen Sie eine Verbindung mit der im Beispiel verwendeten Benutzerdatenbank her.

-- 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: Überprüfen von Rollen auf Serverebene für Microsoft Entra-Anmeldungen

Führen Sie diesen Befehl in der virtuellen master-Datenbank aus, um Microsoft Entra-Anmeldungen anzuzeigen, die Teil von Rollen auf Serverebene in SQL-Datenbank sind. Weitere Informationen zu Microsoft Entra-Serveranmeldungen finden Sie unter Microsoft Entra-Serverprinzipale.

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. Überprüfen der Rollen der virtuellen master-Datenbank auf bestimmte Anmeldungen

Führen Sie diesen Befehl in der virtuellen master-Datenbank aus, um zu überprüfen, ob Rollen in bob vorhanden sind, oder ändern Sie den Wert so, dass er mit Ihrem Prinzipal übereinstimmt.

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

Einschränkungen von Rollen auf Serverebene

  • Es kann bis zu fünf Minuten dauern, bis Rollenzuweisungen wirksam werden. Auch für vorhandene Sitzungen werden Änderungen an Serverrollenzuweisungen erst wirksam, nachdem die Verbindung getrennt und wiederhergestellt wurde. Der Grund ist die verteilte Architektur zwischen der master und den anderen Datenbanken auf demselben logischen Server.

    • Partielle Problemumgehung: Ein Serveradministrator oder ein Microsoft Entra-Administrator kann in den Benutzerdatenbanken, auf die für den Anmeldenamen Zugriff besteht, DBCC FLUSHAUTHCACHE ausführen, um die Wartezeit zu verringern und sicherzustellen, dass Serverrollenzuweisungen in einer Datenbank aktuell sind. Auch gerade angemeldete Benutzer müssen nach dem Ausführen von DBCC FLUSHAUTHCACHE eine neue Verbindung herstellen, damit die Mitgliedschaftsänderungen wirksam werden.

  • IS_SRVROLEMEMBER() wird in der master-Datenbank nicht unterstützt.

Zugehöriger Inhalt