[SCRIPT] – SQL Server Auditing Scripts – One Script to rule them all!

In SQL Server, auditing user permissions can become complex, especially when managing multiple databases and server roles. This script provides a single solution to audit server-level and database-level permissions for all users and logins in your environment.

This script simplifies auditing by providing a comprehensive view of all user permissions in one go. Whether you’re managing SQL Server, Azure SQL, or any other edition, it removes the need for multiple queries or checks. It ensures you have full visibility of explicit permissions without over-complicating the process.

Key Features of the Script:

• Server-Level Permissions:
  Audits server roles and permissions for SQL logins, excluding internal system accounts (e.g., NT AUTHORITY, dbo). It focuses only on explicit permissions and avoids redundancy (like the CONNECT SQL permission).

• Database-Level Permissions:
  Captures explicit permissions granted on database objects (tables, views, schemas) and roles (e.g., db_owner, db_datareader). It ensures GRANT and DENY are clearly reported.

• Public Role Handling:
  Users with no explicit permissions but part of the public role are flagged. This guarantees that no user is left out of the audit, even if they have implicit permissions.

• Advanced Permissions:
  Includes permissions like UNMASK (for Dynamic Data Masking) and VIEW ANY DEFINITION, which are often missed in basic audits.

• No Extra Complexity:
  Unlike other scripts that require multiple runs or checks, this script consolidates everything in one place. It’s designed to be simple, efficient, and easy to use across SQL Server and Azure SQL.

/*
****************************************************************************************************************************************
Script Name:			AUDIT - PERMISSION.sql
Client's Name:			All customers
Create Date:			01/08/2025 - 20:00
Created By:				Bruno Bragatto - Senior DBA (brunobragatto.com)
Objective:				VERSIONE 2012 e SUCCESSIVE: Script per un audit di sicurezza a livello di permessi, sia a livello di istanza che database.
I permessi verificati sono siano i ruoli di istanza (Server Role) che a livello database (Database Role) così come permessi espliciti sui vari oggetti:
Execute, Unmask, Select, DML (Insert, Update e Delete), View Definition etc...
La compatibilità con AzureSQL e Azure Management Instance non è stata ancora validata.
  --------------------------------------------------------------------
  UNIFIED SQL SERVER AUDIT SCRIPT  –  SQL Server 2012 and newer
  --------------------------------------------------------------------
- Server-Level Permissions:
    It retrieves the server roles and permissions granted to SQL Server logins.
    It filters out default roles like guest, dbo, and system-level accounts such as NT AUTHORITY and NT SERVICE to avoid clutter in the audit results.
    Important: The script excludes CONNECT SQL as this is an implicit permission for all logins, and therefore redundant.

- Database-Level Permissions:
    It also audits database roles (such as db_owner, db_datareader, db_accessadmin) and individual permissions granted on database objects (like tables, views, and schemas).
    Explicit GRANT and DENY permissions are displayed for each principal (user or login).

- Public Role Fallback:
    For users or logins that don't have explicit permissions, the script checks whether they belong to the public role, and if so, it lists this role as their permission.
    This ensures that users who might not have specific permissions but are part of a role (like public) are still included in the audit.

- Detailed Object-Level Auditing:
    Permissions such as SELECT, INSERT, and EXECUTE on tables and stored procedures are captured in detail.
    Special attention is given to permissions like UNMASK (for dynamic data masking) and VIEW ANY DEFINITION for database schema objects, which are typically overlooked in basic auditing scripts.

- Exclusion of System-Generated Roles:
    The script excludes internal or system-generated users like MS_DataCollectorInternalUser to focus solely on active users that interact with the system.
----------------------------------------------------------------------------------------------------------------------------------------
Modification Log
Date:													
Modified By:			Bruno Bragatto		
Modification Details:	
Initial Created by:		Bruno Bragatto

****************************************************************************************************************************************/

SET NOCOUNT ON;

--------------------------------------------------------------------------------
-- 0) Drop #Perms
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#Perms') IS NOT NULL 
DROP TABLE #Perms;

--------------------------------------------------------------------------------
-- 1) Create #Perms temp table
--------------------------------------------------------------------------------
CREATE TABLE #Perms
(
  [Scope]            NVARCHAR(128) NOT NULL,
  [Principal]        NVARCHAR(128) NOT NULL,
  [Principal_Type]   NVARCHAR(60),
  [Permission_Type]  NVARCHAR(20),    -- SERVER_ROLE / DATABASE_ROLE / PERMISSION
  [Permission_Name]  NVARCHAR(256),   -- SELECT, EXECUTE, UNMASK, …
  [State]            NVARCHAR(20),    -- GRANT / DENY
  [Securable_Type]   NVARCHAR(60),    -- SERVER / DATABASE / OBJECT / SCHEMA
  [Securable_Schema] NVARCHAR(128),
  [Securable_Name]   NVARCHAR(128)
);

--------------------------------------------------------------------------------
-- 2) SERVER-LEVEL (not considered when Azure SQL DB)
--------------------------------------------------------------------------------
IF CAST(SERVERPROPERTY('EngineEdition') AS INT) <> 5
BEGIN
  -- 2a) membership in server-role
  INSERT INTO #Perms
  SELECT
    CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))             AS [Scope],
    mbr.name        COLLATE DATABASE_DEFAULT                        AS [Principal],
    mbr.type_desc   COLLATE DATABASE_DEFAULT                        AS [Principal_Type],
    'SERVER_ROLE'   COLLATE DATABASE_DEFAULT                        AS [Permission_Type],
    rolep.name      COLLATE DATABASE_DEFAULT                        AS [Permission_Name],
    'MEMBER'                                                        AS [State],
    'SERVER'        COLLATE DATABASE_DEFAULT                        AS [Securable_Type],
    '*','*'
  FROM sys.server_role_members rm
  JOIN sys.server_principals mbr   ON mbr.principal_id   = rm.member_principal_id
  JOIN sys.server_principals rolep ON rolep.principal_id = rm.role_principal_id
  WHERE mbr.type IN ('S','U','G')
    AND mbr.name NOT LIKE '##%'
    AND mbr.name NOT IN ('guest','dbo','sys','INFORMATION_SCHEMA','MS_DataCollectorInternalUser')
    AND mbr.name NOT LIKE 'NT AUTHORITY%' AND mbr.name NOT LIKE 'NT SERVICE%';

-- 2b) Explicit Server Level GRANT/DENY (EXCEPT CONNECT SQL)
INSERT INTO #Perms
SELECT
  CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)),
  sp.name              COLLATE DATABASE_DEFAULT,
  sp.type_desc         COLLATE DATABASE_DEFAULT,
  'PERMISSION'         COLLATE DATABASE_DEFAULT,
  perm.permission_name COLLATE DATABASE_DEFAULT,
  perm.state_desc      COLLATE DATABASE_DEFAULT,
  'SERVER'             COLLATE DATABASE_DEFAULT,
  '*',                 -- Securable_Schema
  '*'                  -- Securable_Name
FROM sys.server_permissions perm
JOIN sys.server_principals sp
  ON sp.principal_id = perm.grantee_principal_id
WHERE perm.permission_name <> 'CONNECT SQL'
  AND sp.type IN('S','U','G')
  AND sp.name NOT LIKE '##%'
  AND sp.name NOT IN('guest','dbo','sys','INFORMATION_SCHEMA','MS_DataCollectorInternalUser')
  AND sp.name NOT LIKE 'NT AUTHORITY%' AND sp.name NOT LIKE 'NT SERVICE%';

--------------------------------------------------------------------------------
-- 3) DATABASE-LEVEL (included master & msdb; excluded model e tempdb)
--------------------------------------------------------------------------------
DECLARE @db  SYSNAME,
        @sql NVARCHAR(MAX);

DECLARE dbcur CURSOR LOCAL FAST_FORWARD FOR
  SELECT name
    FROM sys.databases
   WHERE name NOT IN('model','tempdb')
     AND state_desc = 'ONLINE';

OPEN dbcur;  
FETCH NEXT FROM dbcur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'
    USE ' + QUOTENAME(@db) + N';

    --------------------------------------------------------------------------
    -- 3a) membership in database-role (no public)
    --------------------------------------------------------------------------
    INSERT INTO #Perms
    SELECT
      DB_NAME(),
      mbr.name        COLLATE DATABASE_DEFAULT,
      mbr.type_desc   COLLATE DATABASE_DEFAULT,
      ''DATABASE_ROLE'' COLLATE DATABASE_DEFAULT,
      rolep.name      COLLATE DATABASE_DEFAULT,
      ''MEMBER'',
      ''DATABASE''    COLLATE DATABASE_DEFAULT,
      ''*'',''*''
    FROM sys.database_role_members drm
    JOIN sys.database_principals mbr
      ON mbr.principal_id = drm.member_principal_id
    JOIN sys.database_principals rolep
      ON rolep.principal_id = drm.role_principal_id
    WHERE rolep.name <> ''public''
      AND mbr.type IN(''S'',''U'',''G'',''E'',''X'')
      AND mbr.name NOT LIKE ''##%''
      AND mbr.name NOT IN(''guest'',''dbo'',''sys'',''INFORMATION_SCHEMA'',''MS_DataCollectorInternalUser'')
      AND mbr.name NOT LIKE ''NT AUTHORITY%'' AND mbr.name NOT LIKE ''NT SERVICE%'';

    --------------------------------------------------------------------------
    -- 3b) all explicit permission (classi 0,1,3,6) - except CONNECT DB
    --------------------------------------------------------------------------
    INSERT INTO #Perms
    SELECT
      DB_NAME(),
      dp.name              COLLATE DATABASE_DEFAULT,
      dp.type_desc         COLLATE DATABASE_DEFAULT,
      ''PERMISSION''       COLLATE DATABASE_DEFAULT,
      perm.permission_name COLLATE DATABASE_DEFAULT,
      perm.state_desc      COLLATE DATABASE_DEFAULT,
      CASE 
        WHEN perm.class = 0 THEN ''DATABASE''
        WHEN perm.class = 1 THEN ''OBJECT''
        WHEN perm.class = 3 THEN ''SCHEMA''
        WHEN perm.class = 6 THEN ''OBJECT_OR_COLUMN''
      END                  COLLATE DATABASE_DEFAULT,
      CASE 
        WHEN perm.class IN (1,6) THEN OBJECT_SCHEMA_NAME(perm.major_id)
        WHEN perm.class = 3    THEN SCHEMA_NAME(perm.major_id)
        ELSE ''*''
      END                  COLLATE DATABASE_DEFAULT,
      CASE 
        WHEN perm.class IN (1,6) THEN OBJECT_NAME(perm.major_id)
        ELSE ''*''
      END                  COLLATE DATABASE_DEFAULT
    FROM sys.database_permissions perm
    JOIN sys.database_principals dp
      ON dp.principal_id = perm.grantee_principal_id
    WHERE NOT (perm.permission_name = ''CONNECT'' AND perm.class = 0)
      AND perm.class IN (0,1,3,6)
      AND dp.type IN(''S'',''U'',''G'',''E'',''X'')
      AND dp.name NOT LIKE ''##%''
      AND dp.name NOT IN(''guest'',''dbo'',''sys'',''INFORMATION_SCHEMA'',''MS_DataCollectorInternalUser'')
      AND dp.name NOT LIKE ''NT AUTHORITY%'' AND dp.name NOT LIKE ''NT SERVICE%'';
  ';
  EXEC sp_executesql @sql;
  FETCH NEXT FROM dbcur INTO @db;
END
CLOSE dbcur;  
DEALLOCATE dbcur;

--------------------------------------------------------------------------------
-- 4) PUBLIC fallback
--------------------------------------------------------------------------------
-- 4a) public fallback at Server Level (Instance Level)
INSERT INTO #Perms
SELECT
  CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)),
  sp.name        COLLATE DATABASE_DEFAULT,
  sp.type_desc   COLLATE DATABASE_DEFAULT,
  'SERVER_ROLE'  COLLATE DATABASE_DEFAULT,
  'public'       COLLATE DATABASE_DEFAULT,
  'MEMBER',
  'SERVER'       COLLATE DATABASE_DEFAULT,
  '*','*'
  FROM sys.server_principals sp
  WHERE sp.type IN('S','U','G')
    AND sp.name NOT LIKE '##%'
    AND sp.name NOT IN('guest','dbo','sys','INFORMATION_SCHEMA','MS_DataCollectorInternalUser')
    AND sp.name NOT LIKE 'NT AUTHORITY%' AND sp.name NOT LIKE 'NT SERVICE%'
    AND NOT EXISTS(
      SELECT 1 FROM #Perms p
       WHERE p.Scope     = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128))
         AND p.Principal = sp.name COLLATE DATABASE_DEFAULT
    );
END

-- 4b) Public fallback at Database Level
DECLARE db2 CURSOR LOCAL FAST_FORWARD FOR
  SELECT name
    FROM sys.databases
   WHERE name NOT IN('model','tempdb')
     AND state_desc = 'ONLINE';

OPEN db2;  
FETCH NEXT FROM db2 INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'
    USE ' + QUOTENAME(@db) + N';
    INSERT INTO #Perms
    SELECT
      DB_NAME(),
      dp.name        COLLATE DATABASE_DEFAULT,
      dp.type_desc   COLLATE DATABASE_DEFAULT,
      ''DATABASE_ROLE'' COLLATE DATABASE_DEFAULT,
      ''public''        COLLATE DATABASE_DEFAULT,
      ''MEMBER'',
      ''DATABASE''      COLLATE DATABASE_DEFAULT,
      ''*'',''*''
    FROM sys.database_principals dp
    WHERE dp.type IN(''S'',''U'',''G'',''E'',''X'')
      AND dp.name NOT LIKE ''##%''
      AND dp.name NOT IN(''guest'',''dbo'',''sys'',''INFORMATION_SCHEMA'',''MS_DataCollectorInternalUser'')
      AND dp.name NOT LIKE ''NT AUTHORITY%'' AND dp.name NOT LIKE ''NT SERVICE%''
      AND NOT EXISTS(
        SELECT 1 FROM #Perms p
         WHERE p.Scope     = DB_NAME()
           AND p.Principal = dp.name COLLATE DATABASE_DEFAULT
      );';
  EXEC sp_executesql @sql;
  FETCH NEXT FROM db2 INTO @db;
END
CLOSE db2;  
DEALLOCATE db2;

--------------------------------------------------------------------------------
-- 5) FINAL RESULT
--------------------------------------------------------------------------------
SELECT
  Scope,
  Principal,
  Principal_Type,
  Permission_Type   AS [Type],
  Permission_Name   AS [Role/Permission],
  ISNULL([State], '') AS [State],
  Securable_Type,
  Securable_Schema,
  Securable_Name
FROM #Perms
ORDER BY Scope, Principal, Permission_Type, Permission_Name;
--------------------------------------------------------------------------------
-- 6) DROP #Perms temp table
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#Perms') IS NOT NULL 
DROP TABLE #Perms;