Partager via

sys.database_permissions (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison d’analytique SQL dans Microsoft FabricEntrepôt dans Microsoft FabricBase de données SQL dans Microsoft Fabric Preview

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é.

Column name Data type 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

LISTE DES BIENS À RECHERCHER

IDENTIFIANT DE BASE DE DONNÉES

EXTERNAL LANGUAGE
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) Permission name.
state char(1) Permission state:

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

Database Permissions

Les types d’autorisations suivants sont possibles.

Permission type Permission name S'applique à l'élément sécurisable
AADS ALTERER TOUTE SESSION D'ÉVÉNEMENT DE BASE DE DONNÉES DATABASE
AAMK MODIFIER TOUT MASQUE DATABASE
AEDS MODIFIER TOUTE SOURCE DE DONNÉES EXTERNE DATABASE
AEFF MODIFIER TOUT FORMAT DE FICHIER EXTERNE DATABASE
AL ALTER RÔLE D’APPLICATION, ASSEMBLY, CLÉ ASYMÉTRIQUE, CERTIFICAT, CONTRAT, BASE DE DONNÉES, CATALOGUE FULLTEXT, TYPE DE MESSAGE, OBJET, LIAISON DE SERVICE DISTANT, RÔLE, ROUTE, SCHÉMA, SERVICE, CLÉ SYMÉTRIQUE, UTILISATEUR, COLLECTION DE SCHÉMAS XML
ALAK MODIFIER N’IMPORTE QUELLE CLÉ ASYMÉTRIQUE DATABASE
ALAR MODIFIER N’IMPORTE QUEL RÔLE D’APPLICATION DATABASE
ALAS MODIFIER TOUT ASSEMBLAGE DATABASE
ALCF MODIFIER TOUT CERTIFICAT DATABASE
ALDS MODIFIER TOUT ESPACE DE DONNÉES DATABASE
ALED MODIFIER TOUTE NOTIFICATION D'ÉVÉNEMENT DE BASE DE DONNÉES DATABASE
ALFT MODIFIER N'IMPORTE QUEL CATALOGUE DE TEXTE INTÉGRAL DATABASE
ALMT MODIFIER N’IMPORTE QUEL TYPE DE MESSAGE DATABASE
ALRL MODIFIER N’IMPORTE QUEL RÔLE DATABASE
ALRT MODIFIER TOUTE ROUTE DATABASE
ALSB MODIFIER TOUT LIEN DE SERVICE À DISTANCE DATABASE
ALSC Modifier tout contrat DATABASE
ALSK MODIFIER TOUTE CLÉ SYMÉTRIQUE DATABASE
ALSM MODIFIER TOUT SCHÉMA DATABASE
ALSV MODIFIER N'IMPORTE QUEL SERVICE DATABASE
ALTG ALTERER N'IMPORTE QUEL DÉCLENCHEUR DDL DE BASE DE DONNÉES DATABASE
ALUS MODIFIER TOUS LES UTILISATEURS DATABASE
AUTH AUTHENTICATE DATABASE
BADB BACKUP DATABASE DATABASE
BALO BACKUP LOG DATABASE
CL CONTROL RÔLE D’APPLICATION, ASSEMBLY, CLÉ ASYMÉTRIQUE, CERTIFICAT, CONTRAT, BASE DE DONNÉES, CATALOGUE FULLTEXT, TYPE DE MESSAGE, OBJET, LIAISON DE SERVICE DISTANT, RÔLE, ROUTE, SCHÉMA, SERVICE, CLÉ SYMÉTRIQUE, TYPE, UTILISATEUR, COLLECTION DE SCHÉMAS XML
CO CONNECT DATABASE
CORP CONNECT REPLICATION DATABASE
CP CHECKPOINT DATABASE
CRAG CREATE AGGREGATE DATABASE
CRAK CRÉER UNE CLÉ ASYMÉTRIQUE DATABASE
CRAS CREATE ASSEMBLY DATABASE
CRCF CREATE CERTIFICATE DATABASE
CRDB CREATE DATABASE DATABASE
CRDF CREATE DEFAULT DATABASE
CRED CRÉER UNE NOTIFICATION D'ÉVÉNEMENT DDL DE BASE DE DONNÉES DATABASE
CRFN CREATE FUNCTION DATABASE
CRFT CRÉER UN CATALOGUE DE TEXTE INTÉGRAL DATABASE
CRMT CRÉER UN TYPE DE MESSAGE DATABASE
CRPR CREATE PROCEDURE DATABASE
CRQU CREATE QUEUE DATABASE
CRRL CREATE ROLE DATABASE
CRRT CREATE ROUTE DATABASE
CRRU CREATE RULE DATABASE
CRSB CRÉER UN LIEN DE SERVICE DISTANT DATABASE
CRSC CREATE CONTRACT DATABASE
CRSK CREATE SYMMETRIC KEY (créer une clé symétrique) 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.

CRÉER UNE COLLECTION DE SCHÉMA XML		 DATABASE
DABO ADMINISTRER DES OPÉRATIONS EN BLOC DE BASE DE DONNÉES DATABASE
DL DELETE BASE DE DONNÉES, OBJET, SCHÉMA
EAES EXÉCUTER UN SCRIPT EXTERNE DATABASE
EX EXECUTE ASSEMBLY, DATABASE, OBJECT, SCHEMA, TYPE, XML SCHEMA COLLECTION
IM IMPERSONATE USER
IN INSERT BASE DE DONNÉES, OBJET, SCHÉMA
RC RECEIVE OBJECT
RF REFERENCES ASSEMBLY, ASYMÉTRIQUE KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, SCHEMA, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
SL SELECT BASE DE DONNÉES, OBJET, SCHÉMA
SN SEND SERVICE
SPLN SHOWPLAN DATABASE
SUQN NOTIFICATIONS DE REQUÊTE D’ABONNEMENT DATABASE
TO TAKE OWNERSHIP ASSEMBLY, ASYMÉTRIQUE KEY, CERTIFICATE, CONTRACT, DATABASE, FULLTEXT CATALOG, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SERVICE, SYMMETRIC KEY, TYPE, XML SCHEMA COLLECTION
UP UPDATE BASE DE DONNÉES, OBJET, SCHÉMA
VW VIEW DEFINITION RÔLE D’APPLICATION, ASSEMBLY, CLÉ ASYMÉTRIQUE, CERTIFICAT, CONTRAT, BASE DE DONNÉES, CATALOGUE FULLTEXT, TYPE DE MESSAGE, OBJET, LIAISON DE SERVICE DISTANT, RÔLE, ROUTE, SCHÉMA, SERVICE, CLÉ SYMÉTRIQUE, TYPE, UTILISATEUR, COLLECTION DE SCHÉMAS XML
VWCK AFFICHER LA DÉFINITION DE TOUTE CLÉ DE CHIFFREMENT DE COLONNE DATABASE
VWCM AFFICHER UNE DÉFINITION DE CLÉ PRINCIPALE DE COLONNE DATABASE
VWCT AFFICHER LE SUIVI DES MODIFICATIONS TABLE, SCHEMA
VWDS AFFICHER LE STATUT DE LA BASE DE DONNÉES DATABASE

Autorisations REVOKE et d’exception de colonne

Dans la plupart des cas, la commande REVOKE supprime l’entrée GRANT ou DENY de sys.database_permissions.

Toutefois, il est possible d’accorder ou de refuser des autorisations sur un objet, puis de révoquer cette autorisation sur une colonne. Cette autorisation d’exception de colonne s’affiche sous la forme REVOKE dans sys.database_permissions. Prenons l’exemple suivant :

GRANT SELECT ON Person.Person TO [Sales];

REVOKE SELECT ON Person.Person(AdditionalContactInfo) FROM [Sales];

Ces autorisations s’affichent dans sys.database_permissions sous la forme d’un GRANT (sur la table) et d’une revoke (sur la colonne).

Important

REVOKE est différent de DENY, car le principal Sales peut toujours avoir accès à la colonne via d’autres autorisations. Si nous avons refusé des autorisations plutôt que de les révoquer, Sales ne serait pas en mesure d’afficher le contenu de la colonne, car DENY remplace toujours GRANT.

Permissions

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.

Examples

A. 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.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éesAdventureWorksDW2022:

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

See also

Next steps

  • Last updated on