SQL Server User & Permissions Audit Script

sql-server-users-and-permissions-audit.sql

-- SQL Server User & Permissions Audit Script
USE master
GO

DECLARE
    @errorMessage nvarchar(4000),
    @errorNumber int,
    @errorSeverity int,
    @errorState int,
    @errorLine int,
    @include_database_without_access bit = 0;   -- <---- include/remove MISSING db list

BEGIN TRY
    EXEC AS LOGIN = 'sqllogin' -- or 'DOMAIN\loginname'

    IF OBJECT_ID('tempdb..##myuser') IS NULL
        SELECT DISTINCT @@SERVERNAME '@@servername', DB_NAME() AS dbName,
            principal_id, sid, name, type, usage 
        INTO ##myuser
        FROM sys.user_token WHERE sid IN (SELECT sid FROM sys.database_principals AS dp)
            AND 1 = 2;
    ELSE
        TRUNCATE TABLE ##myuser;

    INSERT INTO ##myuser
    EXECUTE sp_msforeachdb 'Use [?];
        SELECT DISTINCT @@SERVERNAME as [@@servername], DB_NAME() AS dbName,
            principal_id, sid, name, type, usage 
        FROM sys.user_token WHERE sid IN (SELECT sid FROM sys.database_principals AS dp)
            AND name != ''public'';'

    IF @include_database_without_access = 1
    BEGIN
        INSERT INTO ##myuser 
        SELECT DISTINCT @@SERVERNAME as [@@servername], d.name as dbName, NULL AS principal_id,
            NULL AS sid, CASE WHEN d.state_desc = 'ONLINE' THEN 'MISSING' ELSE d.state_desc END AS name,
            'ROLE' AS type, 'MISSING' AS usage  
        FROM sys.databases AS d
        WHERE name NOT IN (SELECT dbName FROM ##myuser AS m);
    END;

    SELECT DISTINCT @@SERVERNAME '@@servername', '[CONNECTION]' AS dbName,
        type AS [ROLE],
        CASE WHEN type = 'WINDOWS GROUP' THEN name ELSE '' END AS [WINDOWS GROUP],
        CASE WHEN type != 'WINDOWS GROUP' THEN name ELSE '' END AS [SQL USER]
    FROM sys.login_token WHERE sid IN (SELECT sid FROM sys.server_principals)
    UNION ALL
    SELECT [@@servername], dbName,
        ISNULL(SUBSTRING(
            (SELECT ',' + t.Name + '' 
            FROM ##myuser t
            WHERE type = 'ROLE'
            AND x.[@@servername] = t.[@@servername]
            AND x.dbName = t.dbName
            ORDER BY t.Name
            FOR XML PATH('')), 2, 200000), '')
        AS [ROLE],
        ISNULL(SUBSTRING(
            (SELECT ',' + t.Name + '' 
            FROM ##myuser t
            WHERE type = 'WINDOWS GROUP'
            AND x.[@@servername] = t.[@@servername]
            AND x.dbName = t.dbName
            ORDER BY t.Name
            FOR XML PATH('')), 2, 200000), '')
        AS [WINDOWS GROUP],
        ISNULL(SUBSTRING(
            (SELECT ',' + t.Name + '' 
            FROM ##myuser t
            WHERE type = 'SQL USER'
            AND x.[@@servername] = t.[@@servername]
            AND x.dbName = t.dbName
            ORDER BY t.Name
            FOR XML PATH('')), 2, 200000), '')
        AS [SQL USER]
    FROM ##myuser x
    GROUP BY [@@servername], dbName
    ORDER BY [@@servername], dbName

    REVERT
END TRY
 
BEGIN CATCH
    SELECT
        @errorNumber = ERROR_NUMBER(),
        @errorSeverity = ERROR_SEVERITY(),
        @errorState = ISNULL(NULLIF(ERROR_STATE(), 0), 1), --don't allow state 0 to be passed in to raiserror
        @errorLine = ERROR_LINE(),
        @errorMessage = N'Error %d, Level %d, State %d.  Error message: ' + ERROR_MESSAGE();
    RAISEERROR(@errorMessage, @errorSeverity, 1, @errorNumber, @errorSeverity, @errorState, @errorLine);
END CATCH;
 
-- Exit Block
SET NOCOUNT OFF;
GO