Azure SQL-Datenbank-Serverrollen für die Berechtigungsverwaltung

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.

Gilt für: Azure SQL-Datenbank

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 7 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 CONNECT-Berechtigung für den*die Datenbankbenutzer*in mit DENY ü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. Es wird empfohlen, diese Serverrolle anstelle der dbmanager-Rolle auf Datenbankebene zu 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. Es wird empfohlen, 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_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.
##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 FREESYSTEMCACHE ('ALL'), DBCC SQLPERF().

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 die geerbten Berechtigungen auf Datenbankebene angezeigt, solange der*die Benutzer*in 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
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateReader## VIEW SERVER STATE, VIEW SERVER PERFORMANCE STATE, VIEW SERVER SECURITY STATE VIEW DATABASE STATE, VIEW DATABASE PERFORMANCE STATE, VIEW DATABASE SECURITY STATE
##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

Verwenden von 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 (Transact-SQL) Metadaten Gibt an, ob ein SQL-Anmeldename Mitglied der angegebenen Rolle auf Serverebene ist.
sys.server_role_members (Transact-SQL) Metadaten Gibt eine Zeile für jedes Mitglied jeder Rolle auf Serverebene zurück.
sys.sql_logins (Transact-SQL) Metadaten Gibt eine Zeile für jeden SQL-Anmeldenamen zurück.
ALTER SERVER ROLE (Transact-SQL) 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. Hinzufügen eines SQL-Anmeldenamens zu einer Rolle auf Serverebene

Im folgenden Beispiel wird der SQL-Anmeldename „Jiao“ der Rolle „##MS_ServerStateReader##“ auf Serverebene hinzugefügt. Diese Anweisung muss in der virtuellen master-Datenbank ausgeführt werden.

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

B. Auflisten aller Prinzipale (SQL-Authentifizierung), 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: Hinzufügen eines Anmeldenamens zu einer Rolle auf Serverebene, Abrufen von Metadaten für die Rollenmitgliedschaft und Berechtigungen und Ausführen einer Testabfrage

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 sehen Sie das Ergebnis.

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

Führen Sie diesen Befehl aus einer Benutzerdatenbank aus.

-- Creating 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 auf Azure AD Anmeldungen

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

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       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 members 
       ON server_role_members.member_principal_id = members.principal_id;

E. Überprüfen der Rollen der virtuellen Masterdatenbank 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 OUTER JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id LEFT OUTER 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 Azure AD-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.

Siehe auch