Ermitteln effektiver Datenbank-Engine-BerechtigungenDetermining Effective Database Engine Permissions

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics JaParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data WarehouseAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics JaParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

In diesem Artikel wird beschrieben, wie Sie feststellen können, wer über Berechtigungen für verschiedene Objekte in der SQL Server-Datenbank-Engine verfügt.This article describes how to determine who has permissions to various objects in the SQL Server Database Engine. SQL Server implementiert zwei Berechtigungssysteme für die Datenbank-Engine.SQL Server implements two permission systems for the Database Engine. Ein älteres System fester Datenbankrollen hat vorkonfigurierte Berechtigungen.An older system of fixed roles has preconfigured permissions. Ab SQL Server 2005 ist ein flexibleres und präziseres System verfügbar.Beginning with SQL Server 2005 a more flexible and precise system is available. (Die Informationen in diesem Artikel gelten für SQL Server ab Version 2005.(The information in this article applies to SQL Server, beginning with 2005. Einige Arten von Berechtigungen sind in einigen Versionen von SQL Server nicht verfügbar.)Some types of permissions are not available in some versions of SQL Server.)

Wichtig

  • Die effektiven Berechtigungen sind das Aggregat von beiden Berechtigungssystemen.The effective permissions are the aggregate of both permission systems.
  • Eine DOS-Berechtigung überschreibt eine Gewährung von Berechtigungen.A denial of permissions overrides a grant of permissions.
  • Wenn ein Benutzer ein Mitglied der festen Serverrolle „sysadmin“ ist, werden Berechtigungen nicht darüber hinaus überprüft, damit Verweigerungen nicht erzwungen werden.If a user is a member of the sysadmin fixed server role, permissions are not checked further, so denials will not be enforced.
  • Die alten und neuen Systeme sind vergleichbar.The old system and new system have similarities. Z.B. ist die Mitgliedschaft in der festen sysadmin-Serverrolle vergleichbar mit der CONTROL SERVER-Berechtigung.For example, membership in the sysadmin fixed server role is similar to having CONTROL SERVER permission. Die Systeme sind jedoch nicht identisch.But the systems are not identical. Wenn eine Anmeldung beispielsweise nur über die CONTROL SERVER-Berechtigung verfügt und eine gespeicherte Prozedur die Mitgliedschaft in der festen sysadmin-Serverrolle überprüft, dann schlägt die Überprüfung der Berechtigung fehl.For example, if a login only has the CONTROL SERVER permission, and a stored procedures check for membership in the sysadmin fixed server role, then the permission check will fail. Das Gegenteil trifft ebenfalls zu.The reverse is also true.

ZusammenfassungSummary

  • Eine Berechtigung auf Serverebene stammt von einer Mitgliedschaft in der festen Serverrolle oder von benutzerdefinierten Serverrollen.Server-level permission can come from membership in the fixed server roles or user-defined server roles. Jeder Benutzer gehört zur festen public-Serverrolle und erhält jede dort zugewiesene Berechtigung.Everyone belongs to the public fixed server role and receives any permission assigned there.
  • Berechtigungen auf Serverebene können von Berechtigungen für Anmeldungen oder benutzerdefinierten Serverrollen stammen.Server-level permissions can come from permission grants to logins or user-defined server roles.
  • Berechtigungen auf Datenbankebene können aus der Mitgliedschaft in festen Datenbankrollen oder benutzerdefinierten Datenbankrollen in jeder Datenbank stammen.Database-level permission can come from membership in the fixed database roles or user-defined database roles in each database. Jeder gehört zu den festen public-Datenbankrollen und empfängt jede zugewiesene Berechtigung.Everyone belongs to the public fixed database role and receives any permission assigned there.
  • Berechtigungen auf Datenbankebene können von Berechtigungen für Benutzer oder benutzerdefinierten Datenbankrollen in jeder Datenbank stammen.Database-level permissions can come from permission grants to users or user-defined database roles in each database.
  • Berechtigungen können von der guest-Anmeldung oder dem aktivierten guest-Datenbankbenutzer empfangen werden.Permissions can be received from the guest login or guest database user if enabled. Die guest-Anmeldenamen und -Benutzer sind standardmäßig deaktiviert.The guest login and users are disabled by default.
  • Windows-Benutzer können Mitglieder der Windows-Gruppen sein, die über Anmeldenamen verfügen können.Windows users can be members of Windows groups that can have logins. SQL Server erfährt von der Windows-Gruppenmitgliedschaft, wenn ein Windows-Benutzer eine Verbindung herstellt und ein Windows-Token mit der Sicherheits-ID einer Windows-Gruppe darstellt.SQL Server learns of Windows group membership when a Windows user connects and presents a Windows token with the security identifier of a Windows group. Da SQL Server automatische Updates nicht über die Windows-Gruppenmitgliedschaften verwaltet oder erhält, kann SQL Server die Berechtigungen der Windows-Benutzer nicht zuverlässig anzeigen, die von der Windows-Gruppenmitgliedschaft empfangen werden.Because SQL Server does not manage or receive automatic updates about Windows group memberships, SQL Server cannot reliably report the permissions of Windows users that are received from Windows group membership.
  • Berechtigungen können erworben werden, indem zu einer Anwendungsrolle gewechselt und das Kennwort bereitgestellt wird.Permissions can be acquired by switching to an application role and providing the password.
  • Berechtigungen können erworben werden, indem eine gespeicherte Prozedur ausgeführt wird, die die EXECUTE AS-Klausel einschließt.Permissions can be acquired by executing a stored procedure that includes the EXECUTE AS clause.
  • Berechtigungen können mit der IMPERSONATE-Berechtigung durch Anmeldenamen oder Benutzer erworben werden.Permissions can be acquired by logins or users with the IMPERSONATE permission.
  • Mitglieder der Administratorgruppe des lokalen Computers können immer ihre Berechtigungen auf sysadmin erhöhen.Members of the local computer administrator group can always elevate their privileges to sysadmin. (Gilt nicht für die SQL-Datenbank.)(Does not apply to SQL Database.)
  • Mitglieder der festen securityadmin-Serverrolle können viele ihrer Berechtigungen und in einigen Fällen auch die Berechtigungen auf sysadmin erhöhen.Members of the securityadmin fixed server role can elevate many of their privileges and in some cases can elevate the privileges to sysadmin. (Gilt nicht für die SQL-Datenbank.)(Does not apply to SQL Database.)
  • SQL Server-Administratoren können Informationen zu allen Anmeldungen und Benutzern anzeigen.SQL Server administrators can see information about all logins and users. Weniger privilegierten Benutzern werden in der Regel nur Informationen zu ihren eigenen Identitäten angezeigt.Less privileged users usually see information about only their own identities.

Ältere feste RollenberechtigungssystemeOlder Fixed Role Permission System

Feste Serverrollen und feste Datenbankrollen verfügen über vorkonfigurierte Berechtigungen, die nicht geändert werden können.Fixed Server Roles and Fixed Database Roles have preconfigured permissions that cannot be changed. Führen Sie die folgende Abfrage aus, um zu bestimmen, wer Mitglied der festen Serverrolle ist:To determine who is a member of a fixed server role, execute the following query:

Hinweis

Dies gilt nicht für SQL-Datenbank oder SQL Data Warehouse, bei denen die Berechtigung auf Serverebene nicht verfügbar ist.Does not apply to SQL Database or SQL Data Warehouse where server level permission is not available. Die is_fixed_role-Spalte von sys.server_principals wurde zu SQL Server 2012 hinzugefügt.The is_fixed_role column of sys.server_principals was added in SQL Server 2012. Sie ist für ältere Versionen von SQL Server nicht erforderlich.It is not needed for older versions of SQL Server.

SELECT SP1.name AS ServerRoleName, 
 isnull (SP2.name, 'No members') AS LoginName   
 FROM sys.server_role_members AS SRM
 RIGHT OUTER JOIN sys.server_principals AS SP1
   ON SRM.role_principal_id = SP1.principal_id
 LEFT OUTER 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;

Hinweis

  • Alle Anmeldenamen sind Mitglieder der öffentlichen Rollen und können nicht entfernt werden.All logins are members of the public role and cannot be removed.
  • Diese Abfrage überprüft die Tabellen in der Master-Datenbank, sie kann jedoch in jeder Datenbank für das lokale Produkt ausgeführt werden.This query checks tables in the master database but it can be executed in any database for the on premises product.

Um zu bestimmen, wer die Mitglieder einer festen Datenbankrolle sind, führen Sie die folgende Abfrage in jeder Datenbank aus.To determine who is a member of a fixed database role, execute the following query in each database.

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

Um die Berechtigungen zu verstehen, die jeder Rolle gewährt werden, finden Sie Rollenbeschreibungen und Illustrationen in der Onlinedokumentation (Rollen auf Serverebene und Rollen auf Datenbankebene).To understand the permissions that are granted to each role, see the role descriptions at illustrations in Books Online (Server-Level Roles, and Database-Level Roles).

Neuere präzise BerechtigungssystemeNewer Granular Permission System

Dieses System ist flexibel, was bedeutet, dass es kompliziert sein kann, wenn die Personen, die es einrichten, präzise sein möchten.This system is flexible, which means it can be complicated if the people setting it up want to be precise. Zur Vereinfachung könnten Sie Rollen erstellen, Rollen Berechtigungen zuweisen und dann Personengruppen zu den Rollen hinzufügen.To simplify matters it helps to create roles, assign permissions to roles, and then add groups of people to the roles. Und es ist einfacher, wenn das Entwicklungsteam für die Datenbank die Aktivitäten nach Schema trennt und dann die Rollenberechtigungen für ein ganzes Schema statt für einzelne Tabellen oder Prozeduren erteilt.And it's easier if the database development team separates activity by schema and then grants role permissions to a whole schema instead of to individual tables or procedures. Reale Szenarios sind komplex, und Geschäftsanforderungen können unerwartete Sicherheitsanforderungen schaffen.Real world scenarios are complex and business needs can create unexpected security requirements.

Die folgende Grafik zeigt die Berechtigungen und ihre Beziehungen zueinander.The following graphic shows the permissions and their relationships to each other. Einige der Berechtigungen auf höherer Ebene (z.B. CONTROL SERVER) sind mehrmals aufgeführt.Some of the higher level permissions (such as CONTROL SERVER) are listed many times. In diesem Artikel ist nicht ausreichend Platz, um das Poster entsprechend darzustellen.In this article, the poster is far too small to read. Klicken Sie auf das Bild, um das Poster zu den Datenbank-Engine-Berechtigungen im PDF-Format herunterzuladen.Click the image to download the Database Engine Permissions Poster in pdf format.

DatenbankmodulberechtigungenDatabase Engine Permissions

SicherheitsklassenSecurity Classes

Berechtigungen können auf Serverebene, Datenbankebene, Schemaebene oder Objektebene usw. erteilt werden. Es gibt 26 Ebenen (genannt Klassen).Permissions can be granted at the server-level, the database-level, the schema-level, or the object-level, etc. There are 26 levels (called classes). Die vollständige Liste der Klassen in alphabetischer Reihenfolge lautet: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASE SCOPED 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 und XML SCHEMA COLLECTION.The complete list of classes in alphabetic order is: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASE SCOPED 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. (Einige Klassen sind für bestimmte Typen von SQL Server nicht verfügbar.) Vollständige Informationen zu jeder Klasse erfordern eine andere Abfrage.(Some classes are not available on some types of SQL Servers.) To provide full information about each class requires a different query.

PrincipalsPrincipals

Dateiberechtigungen werden an Prinzipale erteilt.Permissions are granted to principals. Prinzipale können Serverrollen, Anmeldenamen, Datenbankrollen oder Benutzer sein.Principals can be server roles, logins, database roles, or users. Anmeldungen können Windows-Gruppen darstellen, die viele Windows-Benutzer enthalten.Logins can represent Windows groups that include many Windows users. Da Windows-Gruppen nicht von SQL Server verwaltet werden, weiß SQL Server nicht immer, wer Mitglied einer Windows-Gruppe ist.Since Windows groups are not maintained by SQL Server, SQL Server does not always know who is a member of a Windows group. Wenn ein Windows-Benutzer mit SQL Server verbunden ist, enthält das Anmeldungspaket das Token der Windows-Gruppenmitgliedschaft für den Benutzer.When a Windows user connects to SQL Server, the login packet contains the Windows group membership tokens for the user.

Wenn ein Windows-Benutzer eine Verbindung mithilfe einer Anmeldung auf Basis einer Windows-Gruppe herstellt, könnten einige Aktivitäten SQL Server benötigen. Dieser würde eine Anmeldung oder einen Benutzer erstellen, der die einzelnen Windows-Benutzer darstellt.When a Windows user connects using a login based on a Windows group, some activities may require SQL Server to create a login or user to represent the individual Windows user. Z.B. enthält eine Windows-Gruppe (Techniker) Benutzer (Mary, Todd, Pat) und die Techniker-Gruppe verfügt über ein Datenbankbenutzerkonto.For example, a Windows group (Engineers) contains users (Mary, Todd, Pat) and the Engineers group has a database user account. Wenn Mary über eine Berechtigung verfügt und eine Tabelle erstellt kann ein Benutzer (nämlich Mary) als Besitzer der Tabelle erstellt werden.If Mary has permission and creates a table, a user (Mary) might be created to be the owner of the table. Wenn Todd eine Berechtigung verweigert wird, über die der Rest der Techniker-Gruppe verfügt, dann muss der Benutzer Todd die Möglichkeit erhalten, die Berechtigungsverweigerung nachverfolgen zu können.Or if Todd is denied a permission that the rest of the Engineers group has, then the user Todd must be created to track the permission denial.

Denken Sie daran, dass ein Windows-Benutzer Mitglied von mehr als einer Windows-Gruppe (z.B. sowohl Techniker und Manager) sein könnte.Remember that a Windows user might be a member of more than one Windows group (e.g. both Engineers, and Managers). Berechtigungen, die der Techniker- oder Manager-Anmeldung, den individuellen Benutzern oder Rollen, in denen der Benutzer Mitglied ist, gewährt oder verweigert werden, werden alle aggregiert und für die effektiven Berechtigungen ausgewertet.Permissions granted or denied to the Engineers login, to the Managers login, granted or denied to the user individually, and granted or denied to roles that the user is a member of, will all be aggregated and evaluated to for the effective permissions. Die HAS_PERMS_BY_NAME-Funktion kann anzeigen, ob ein Benutzer oder Anmeldename über eine bestimmte Berechtigung verfügt.The HAS_PERMS_BY_NAME function can reveal whether a user or login has a particular permission. Es gibt jedoch keine offensichtliche Möglichkeit zur Bestimmung der Quelle der Erteilung oder Verweigerung der Berechtigung.However, there is no obvious way of determining the source of the grant or denial of permission. Durchsuchen Sie die Liste der Berechtigungen, und probieren Sie sie vielleicht auch aus.Study the list of permissions and perhaps experiment using trial and error.

Nützliche AbfragenUseful Queries

ServerberechtigungenServer Permissions

Die folgende Abfrage gibt eine Liste der Berechtigungen zurück, die auf Serverebene erteilt oder verweigert wurden.The following query returns a list of the permissions that have been granted or denied at the server level. Diese Abfrage sollte in der Master-Datenbank ausgeführt werden.This query should be executed in the master database.

Hinweis

Berechtigungen auf Serverebene können nicht auf SQL-Datenbank oder SQL Data Warehouse abgefragt oder erteilt werden.Server-level permissions cannot be granted or queried on SQL Database or SQL Data Warehouse.

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

DatenbankberechtigungenDatabase Permissions

Die folgende Abfrage gibt eine Liste der Berechtigungen zurück, die auf Datenbankebene erteilt oder verweigert wurden.The following query returns a list of the permissions that have been granted or denied at the database level. Diese Abfrage sollte in jeder Datenbank ausgeführt werden.This query should be executed in each database.

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

Jede Klasse der Berechtigungstabelle kann mit anderen Systemansichten verknüpft werden, die verwandte Informationen über die Klasse des sicherungsfähigen Elements bereitstellen.Each class of permission the permission table can be joined to other system views that provide related information about that class of securable. Die folgende Abfrage enthält z.B. den Namen des Datenbankobjekts, das von der Berechtigung betroffen ist.For example, the following query provides the name of the database object that is affected by the permission.

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
 JOIN sys.database_permissions AS pe
   ON pr.principal_id = pe.grantee_principal_id
 JOIN sys.objects AS oj
   ON oj.object_id = pe.major_id
 JOIN sys.schemas AS s
   ON oj.schema_id = s.schema_id
 WHERE class_desc = 'OBJECT_OR_COLUMN';

Verwenden Sie die HAS_PERMS_BY_NAME-Funktion, um zu bestimmen, ob ein bestimmter Benutzer (in diesem Fall TestUser) über eine Berechtigung verfügt.Use the HAS_PERMS_BY_NAME function to determine if a particular user (in this case TestUser) has a permission. Beispiel:For example:

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

Die Details der Syntax finden Sie unter HAS_PERMS_BY_NAME.For the details of the syntax, see HAS_PERMS_BY_NAME.

Siehe auch:See Also:

Erste Schritte mit Berechtigungen für die Datenbank-Engine Getting Started with Database Engine Permissions
Tutorial: Erste Schritte mit der Datenbank-EngineTutorial: Getting Started with Database Engine