Etablir la liste des privilèges

Un utilisateur (au niveau base de données) peut recevoir des privilèges de différentes façon. Il n'est pas toujours facile d'établir la liste exact des privilèges qui lui sont réellement accordés. Toutes les informations sont contenues dans les vues/tables systèmes et en les interrogeant il est possible d'établir cette liste.

Les vues systèmes utilisés sont:
• sys.database_principals: Afin d'établir la correspondance entre un utilisateur, rôle, ... et son identifiant
• sys.database_permissions: Afin d'établir la liste des privilèges accordés
• sys.schemas: Liste des schémas
• sys.database_role_members: Pour établir les rôles de base de données accordés à un utilisateur

La fonction créé var compléter une table en y ajoutant:
Les privilèges accordés directement
Les privilèges accordés au niveau schéma
Les appartenances à un ou plusieurs rôle de base de données

Utilisation de la fonction pour établir la liste des privilèges accordés à l'utilisateur de base de données Paul:

select * from dbo.affichePrivileges('Paul')

Cette fonction peut également être utilisé pour établir la liste des privilèges accordés directement à un rôle de base de données

create function dbo.affichePrivileges(@nom varchar(80)) 
  returns @retour TABLE(
    class_desc nvarchar(120),      type char(4),
    permission_name nvarchar(256), state char(1),
    state_desc nvarchar(120), object_name nvarchar(120)) 
as
begin
  --Privilèges accordés directement
  insert into @retour
  select class_desc, permissions.type, permission_name, 
         state, state_desc, object_name(major_id)
    from sys.database_principals principals
    inner join sys.database_permissions permissions
    on permissions.grantee_principal_id=principals.principal_id
    where principals.name=@nom and class!=3;
  --Privilèges accordés au niveau schéma
  insert into @retour
  select class_desc, permissions.type, permission_name, 
         state, state_desc, schemas.name
    from sys.database_principals principals
    inner join sys.database_permissions permissions
    on permissions.grantee_principal_id=principals.principal_id
    inner join sys.schemas schemas
    on permissions.major_id=schemas.schema_id
    where principals.name=@nom and class=3;
  --Rôles accordés
  insert into @retour
  select 'ROLE','RO','','G','GRANT',role_name.name
    from sys.database_role_members role_member
    inner join sys.database_principals utilisateur
    on role_member.member_principal_id=utilisateur.principal_id
    inner join sys.database_principals role_name
    on role_member.role_principal_id=role_name.principal_id
    where utilisateur.name=@nom;
  return;
end;
go
select * from dbo.affichePrivileges('Paul');