Partage via

sys.database_permissions (Transact-SQL)

  • Article

S’applique à : point de terminaison d’analytique SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL Analytics dans Microsoft Fabric Warehouse dans Microsoft Fabric

Renvoie une ligne pour chaque autorisation ou chaque autorisation avec exception sur colonne dans la base de données. Pour les colonnes, il existe une ligne pour chaque autorisation différente de l'autorisation correspondante au niveau objet. Si l’autorisation sur la colonne est identique à l’autorisation correspondante au niveau objet, aucune ligne n’existe et l’autorisation utilisée est celle de l’objet.

Important

Les autorisations au niveau colonne remplacent les autorisations au niveau objet sur la même entité.

Nom de la colonne Type de données Description
class tinyint Identifie la classe sur laquelle l'autorisation existe. Pour plus d’informations, consultez sys.securable_classes (Transact-SQL).

0 = Base de données
1 = Objet ou colonne
3 = Schéma
4 = Principal de la base de données
5 = Assembly - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
6 = Type
10 = Collection de schémas XML -
S’applique à : SQL Server 2008 (10.0.x) et ultérieur.
15 = Type de message : SQL Server 2008 (10.0.x) et versions ultérieures.
16 = Contrat de service - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
17 = Service - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
18 = Liaison de service distant - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
19 = Route - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
23 =Catalogue de texte intégral - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
24 = Clé symétrique : SQL Server 2008 (10.0.x) et versions ultérieures.
25 = Certificat - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
26 = Clé asymétrique - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
29 = Liste de mots vides en texte intégral - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
31 = Search Property List - S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures.
32 = Informations d’identification délimitées à la base de données : SQL Server 2016 (13.x) et versions ultérieures.
34 = Langage externe - S’applique à : SQL Server 2019 (15.x) et versions ultérieures.
class_desc nvarchar(60) Description de la classe sur laquelle l'autorisation existe.

DATABASE

OBJECT_OR_COLUMN

SCHEMA

DATABASE_PRINCIPAL

ASSEMBLY

TYPE

XML_SCHEMA_COLLECTION

MESSAGE_TYPE

SERVICE_CONTRACT

SERVICE

REMOTE_SERVICE_BINDING

ROUTE

FULLTEXT_CATALOG

SYMMETRIC_KEYS

CERTIFICATE

ASYMMETRIC_KEY

FULLTEXT STOPLIST

SEARCH PROPERTY LIST

DATABASE SCOPED CREDENTIAL

LANGAGE EXTERNE
major_id int ID de l'objet sur lequel l'autorisation existe, interprété en fonction de la classe. En règle générale, le major_id simple type d’ID qui s’applique à ce que représente la classe.

0 = La base de données elle-même

>0 = ID d’objet pour les objets utilisateur

<0 = Object-ID pour les objets système
minor_id int ID secondaire de l'objet sur lequel l'autorisation existe, interprété en fonction de la classe. Souvent, la minor_id valeur est zéro, car il n’y a pas de sous-catégorie disponible pour la classe d’objet. Sinon, il s’agit de l’ID de colonne d’une table.
grantee_principal_id int ID du principal de la base de données à laquelle les autorisations sont accordées.
grantor_principal_id int ID du principal de la base de données du fournisseur de ces autorisations.
type char(4) Type d'autorisation de la base de données. Pour obtenir la liste des types d'autorisations, consultez le tableau ci-dessous.
permission_name nvarchar(128) Nom de l’autorisation.
state char(1) État de l'autorisation :

D = Refusée

R = Révoquée

G = Accordée

W = Accordée avec option Grant
state_desc nvarchar(60) Description de l'état de l'autorisation :

DENY

REVOKE

GRANT

GRANT_WITH_GRANT_OPTION

Autorisations de base de données

Les types d’autorisations suivants sont possibles.

Type d'autorisation Nom de l'autorisation S'applique à l'élément sécurisable
AADS ALTER ANY DATABASE EVENT SESSION DATABASE
AAMK ALTER ANY MASK DATABASE
AEDS ALTER ANY EXTERNAL DATA SOURCE DATABASE
AEFF ALTER ANY EXTERNAL FILE FORMAT DATABASE
AL ALTER APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, USER, XML SCHEMA COLLECTION
ALAK ALTER ANY ASYMMETRIC KEY DATABASE
ALAR ALTER ANY APPLICATION ROLE DATABASE
ALAS ALTER ANY ASSEMBLY DATABASE
ALCF ALTER ANY CERTIFICATE DATABASE
ALDS ALTER ANY DATASPACE DATABASE
ALED ALTER ANY DATABASE EVENT NOTIFICATION DATABASE
ALFT ALTER ANY FULLTEXT CATALOG DATABASE
ALMT ALTER ANY MESSAGE TYPE DATABASE
ALRL ALTER ANY ROLE DATABASE
ALRT ALTER ANY ROUTE DATABASE
ALSB ALTER ANY REMOTE SERVICE BINDING DATABASE
ALSC ALTER ANY CONTRACT DATABASE
ALSK ALTER ANY SYMMETRIC KEY DATABASE
ALSM ALTER ANY SCHEMA DATABASE
ALSV ALTER ANY SERVICE DATABASE
ALTG ALTER ANY DATABASE DDL TRIGGER DATABASE
ALUS ALTER ANY USER DATABASE
AUTH AUTHENTICATE DATABASE
BADB BACKUP DATABASE DATABASE
BALO BACKUP LOG DATABASE
CL CONTROL APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION
CO CONNECT DATABASE
CORP CONNECT REPLICATION DATABASE
CP CHECKPOINT DATABASE
CRAG CREATE AGGREGATE DATABASE
CRAK CREATE ASYMMETRIC KEY DATABASE
CRAS CREATE ASSEMBLY DATABASE
CRCF CREATE CERTIFICATE DATABASE
CRDB CREATE DATABASE DATABASE
CRDF CREATE DEFAULT DATABASE
CRED CREATE DATABASE DDL EVENT NOTIFICATION DATABASE
CRFN CREATE FUNCTION DATABASE
CRFT CREATE FULLTEXT CATALOG DATABASE
CRMT CREATE MESSAGE TYPE DATABASE
CRPR CREATE PROCEDURE DATABASE
CRQU CREATE QUEUE DATABASE
CRRL CREATE ROLE DATABASE
CRRT CREATE ROUTE DATABASE
CRRU CREATE RULE DATABASE
CRSB CREATE REMOTE SERVICE BINDING DATABASE
CRSC CREATE CONTRACT DATABASE
CRSK CREATE SYMMETRIC KEY DATABASE
CRSM CREATE SCHEMA DATABASE
CRSN CREATE SYNONYM DATABASE
CRSO S’applique à : SQL Server 2012 (11.x) et versions ultérieures.

CREATE SEQUENCE		 DATABASE
CRSV CREATE SERVICE DATABASE
CRTB CREATE TABLE DATABASE
CRTY CREATE TYPE DATABASE
CRVW CREATE VIEW DATABASE
CRXS S’applique à : SQL Server 2008 (10.0.x) et ultérieur.

CREATE XML SCHEMA COLLECTION		 DATABASE
DABO ADMINISTER DATABASE BULK OPERATIONS DATABASE
DL Suppression DATABASE, OBJECT, SCHEMA
EAES EXECUTE ANY EXTERNAL SCRIPT DATABASE
EX Exécutez ASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTION
IM IMPERSONATE Utilisateur
IN INSERT DATABASE, OBJECT, SCHEMA
RC RECEIVE OBJECT
RF REFERENCES ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
SL SELECT DATABASE, OBJECT, SCHEMA
SN SEND SERVICE
SPLN SHOWPLAN DATABASE
SUQN SUBSCRIBE QUERY NOTIFICATIONS DATABASE
TO TAKE OWNERSHIP ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
UP UPDATE DATABASE, OBJECT, SCHEMA
VW VIEW DEFINITION APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION
VWCK VIEW ANY COLUMN ENCRYPTION KEY DEFINITION DATABASE
VWCM VIEW ANY COLUMN MASTER KEY DEFINITION DATABASE
VWCT VIEW CHANGE TRACKING TABLE, SCHEMA
VWDS VIEW DATABASE STATE DATABASE

Autorisations

Tout utilisateur peut consulter ses propres autorisations. Pour afficher les autorisations d'autres utilisateurs, vous devez disposer de l'autorisation VIEW DEFINITION, ALTER ANY USER, ou de n'importe quelle autorisation sur un utilisateur. Pour afficher les rôles définis par l'utilisateur, vous devez disposer de l'autorisation ALTER ANY ROLE, ou appartenir au rôle (notamment public).

La visibilité des métadonnées dans les affichages catalogue est limitée aux éléments sécurisables qu'un utilisateur détient ou pour lesquels des autorisations lui ont été accordées. Pour plus d'informations, consultez Metadata Visibility Configuration.

Exemples

R. Répertorier toutes les autorisations des principaux de base de données

La requête suivante énumère les autorisations accordées ou refusées explicitement aux principaux de base de données.

Important

Les autorisations de rôles de base de données fixes n’apparaissent pas dans sys.database_permissions. Par conséquent, les principaux de base de données peuvent avoir des autorisations supplémentaires non répertoriées ici.

SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name  
FROM sys.database_principals AS pr  
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;

B. Répertorier les autorisations sur les objets de schéma dans une base de données

La requête suivante joint sys.database_principals et aux sys.objects et sys.schemas pour répertorier les autorisations accordées ou refusées à des objets de schéma spécifiques.sys.database_permissions

SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id;

C. Répertorier les autorisations pour un objet spécifique

Vous pouvez utiliser l’exemple précédent pour interroger des autorisations spécifiques à un objet de base de données unique.

Par exemple, considérez les autorisations granulaires suivantes accordées à un utilisateur test de base de données dans l’exemple de base de données AdventureWorksDW2022:

GRANT SELECT ON dbo.vAssocSeqOrders TO [test];

Recherchez les autorisations granulaires affectées à dbo.vAssocSeqOrders:

SELECT pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE o.name = 'vAssocSeqOrders'
    AND s.name = 'dbo';

Retourne la sortie :

principal_id    name    type_desc    authentication_type_desc    state_desc    permission_name    ObjectName
5    test    SQL_USER    INSTANCE    GRANT    SELECT    dbo.vAssocSeqOrders

Voir aussi

Étapes suivantes