SQL Server Access Audit: A Comprehensive Script - Azure SQL Compatible

 

In any SQL Server environment, effective access management is crucial for maintaining data security and ensuring compliance. Over time, users, groups, and roles are assigned various permissions, which can become difficult to track. Whether for auditing or enforcing the principle of least privilege, knowing exactly who has access to what resources in your database is essential.

The following script simplifies the process by listing all access provisioned to SQL users, Windows users, or groups—whether directly or through roles. This query has been tested across different environments, including SQL Server 2005-2019, Azure SQL Managed Instance (MI), and Azure SQL Databases, making it versatile for a range of SQL platforms.

The script helps administrators by pulling detailed access information at both the table and column levels, making it easier to audit and manage user access across the database.

Copied!

WITH DB_CPU_Stats AS (
   -- List all access provisioned to a SQL user or Windows user/group directly 
SELECT
    [UserName] = princ.[name],
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'                -- SQL Server authenticated user
                    WHEN 'U' THEN 'Windows User'            -- Windows authenticated user
                    WHEN 'G' THEN 'Windows Group'           -- Windows group
                    WHEN 'A' THEN 'Application Role'        -- Application role
                    WHEN 'X' THEN 'External User'           -- External user (e.g., Azure AD)
                    WHEN 'Y' THEN 'External Group'          -- External group (e.g., Azure AD)
                    ELSE princ.[type]
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = NULL,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,    
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    -- Database user
    sys.database_principals princ
    LEFT JOIN
    -- Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
    LEFT JOIN
    -- Table columns
    sys.columns col ON col.[object_id] = perm.major_id
        AND col.[column_id] = perm.[minor_id]
    LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] IN ('S','U','G','A','X','Y')  -- Exclude 'R' for Database Roles
UNION
-- List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
    [UserName] = memberprinc.[name],
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'G' THEN 'Windows Group'
                    WHEN 'A' THEN 'Application Role'
                    WHEN 'X' THEN 'External User'
                    WHEN 'Y' THEN 'External Group'
                    ELSE memberprinc.[type]
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    -- Role/member associations
    sys.database_role_members members
    JOIN
    -- Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    JOIN
    -- Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
    LEFT JOIN
    -- Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
    -- Table columns
    sys.columns col on col.[object_id] = perm.major_id
        AND col.[column_id] = perm.[minor_id]
    LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    memberprinc.[type] IN ('S','U','G','A','X','Y')  -- Exclude 'R' for Database Roles
UNION
-- List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    -- Roles
    sys.database_principals roleprinc
    LEFT JOIN
    -- Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN
    -- Table columns
    sys.columns col on col.[object_id] = perm.major_id
        AND col.[column_id] = perm.[minor_id]
    JOIN
    -- All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    -- Only roles
    roleprinc.[type] = 'R' AND
    -- Only public role
    roleprinc.[name] = 'public' AND
    -- Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc;

To learn how to script database-level permissions, check out my other post.

https://www.dbascrolls.com/2023/02/script-db-level-permissions.html