Elenco degli utenti con i relativi permessi

[FrancyDotNet]

Questo script SQL consente di ottenere un elenco degli utenti del database insieme ai relativi permessi. È utile per monitorare e gestire le autorizzazioni degli utenti nel database SQL Server.

Codice: Seleziona tutto

SELECT 
    princ.name AS PrincipalName,
    princ.type_desc AS PrincipalType,
    perm.permission_name AS PermissionName,
    perm.state_desc AS PermissionState,
    obj.name AS ObjectName
FROM 
    sys.database_permissions perm
LEFT JOIN 
    sys.all_objects obj ON perm.major_id = obj.object_id
LEFT JOIN 
    sys.database_principals princ ON perm.grantee_principal_id = princ.principal_id
WHERE 
    princ.name IS NOT NULL
ORDER BY 
    PrincipalName, ObjectName, PermissionName;

Seconda versione:

Codice: Seleziona tutto

-- Elenco degli utenti, ruoli e certificati con relativi permessi
SELECT 
    pr.name AS [PrincipalName],
    pr.type_desc AS [PrincipalType],
    perm.permission_name AS [Permission],
    perm.state_desc AS [PermissionState],
    obj.name AS [ObjectName],
    obj.type_desc AS [ObjectType],
    schema_name(obj.schema_id) AS [Schema]
FROM 
    sys.database_principals AS pr
LEFT JOIN 
    sys.database_permissions AS perm ON perm.grantee_principal_id = pr.principal_id
LEFT JOIN 
    sys.objects AS obj ON obj.object_id = perm.major_id
ORDER BY 
    pr.name, obj.name, perm.permission_name;