A SQL Server Permission Report in PowerBI

What it is and how to get it

When working with SQL Instances that have lots of databases, sensitive data and equally lots of users/applications that accesses those, it can get pretty troublesome to keep track of every user, for every database, down to the single column/action.
That’s why I developed this report, in orde to be able to quickly determine the security status of each object or user in the system.

The granting information can be filtered for each Database, Object, Object Column, Type of object, User, Type of Permission and Permission State, and it returns information on everything that may interest you in auditing this kind of information, including the user roles (if it has any).

The neat part of this report is that is completely self-contained, there is no need for any external procedure or component to live on the server, you can just publish the Report to the cloud and have it refreshed through the PowerBI gateway (and a user that has access to the security DMV, of course).
If you don’t have permissions to fetch this kind of information from all the databases, those will be skipped without blocking the output for the others.

You can get the report from my GitHub here

Technical information ahead

There are basically 4 queries in the PowerBI Model, two of them trivial; You can reuse the code below as a way to extract all the database permissions (and user roles) for each database in your instance:

Database Permissions

It’s the core of the report, basically loops via Dynamic SQL through all the databases, skipping those where the user running the report doesn’t have the right privileges.

SET NOCOUNT ON;

DECLARE @code nvarchar(max) = 'SET XACT_ABORT OFF;';
SET @code += CHAR(10);

IF (OBJECT_ID('tempdb..##database_principals') IS NOT NULL)
    DROP TABLE  ##database_principals

IF (OBJECT_ID('tempdb..##database_permissions') IS NOT NULL)
    DROP TABLE  ##database_permissions

IF (OBJECT_ID('tempdb..##database_roles') IS NOT NULL)
    DROP TABLE  ##database_roles


CREATE TABLE ##database_principals
(
 [DBname]                   SYSname NULL,
 [name]                     SYSname NULL,
 [principal_id]             INT NULL,
 [type]                     CHAR(1) NULL,
 [type_desc]                NVARCHAR(60) NULL,
 [default_schema_name]      SYSname NULL,
 [create_date]              DATETIME NULL,
 [modify_date]              DATETIME NULL,
 [owning_principal_id]      INT NULL,
 [sid]                      VARBINARY(85) NULL,
 [is_fixed_role]            BIT NULL,
 [authentication_type]      INT NULL,
 [authentication_type_desc] NVARCHAR(60) NULL,
 [default_language_name]    SYSname NULL,
 [default_language_lcid]    INT NULL
);


INSERT INTO ##database_principals
       SELECT 'Master' AS [DBname],
              [name],
              [principal_id],
              [type],
              [type_desc],
              [default_schema_name],
              [create_date],
              [modify_date],
              [owning_principal_id],
              [sid],
              [is_fixed_role],
              [authentication_type],
              [authentication_type_desc],
              [default_language_name],
              [default_language_lcid]
       FROM   master.sys.database_principals;

SELECT @code += 'BEGIN TRY' + CHAR(10) +
       'INSERT INTO ##database_principals' + CHAR(10) +
       'SELECT ''' + name + ''' as DBname, 
              [name],
              [principal_id],
              [type],
              [type_desc],
              [default_schema_name],
              [create_date],
              [modify_date],
              [owning_principal_id],
              [sid],
              [is_fixed_role],
              [authentication_type],
              [authentication_type_desc],
              [default_language_name],
              [default_language_lcid]' + CHAR(10)+
       'FROM ' + QUOTEname(name) + '.sys.database_principals;' + CHAR(10) +
       'END TRY' + CHAR(10) +
       'BEGIN CATCH PRINT ''Cannot Read database_principals information from ' + name + '''; END CATCH' + CHAR(10)
FROM sys.databases
WHERE [name] <> 'Master'

EXEC sp_executesql @code


CREATE TABLE ##database_permissions
([DBname]          SYSname NULL,
 [class_desc]      NVARCHAR(60) NULL,
 [Object name]     SYSname NULL,
 [Grantee name]    SYSname NULL,
 [Grantor name]    SYSname NULL,
 [permission_name] NVARCHAR(128) NULL,
 [state_desc]      NVARCHAR(60) NULL,
 [Column]          SYSname NULL,
 [IsSystem]        CHAR(3)
);

INSERT INTO ##database_permissions
SELECT 'Master' as DBname,
       [PERM].[class_desc],
       OBJECT_name([PERM].[major_id]) AS [Object name],
       [GRANTEE].[name] AS [Grantee name],
       [GRANTOR].[name] AS [Grantor name],
       [PERM].[permission_name],
       [state_desc],
       [COL].[name] AS [Column],
       CASE
           WHEN [PERM].[major_id] < 0 THEN 'Yes'
           ELSE 'No'
       END AS [IsSystem]
FROM   sys.database_permissions PERM
JOIN ##database_principals GRANTEE ON GRANTEE.DBname = 'Master' AND GRANTEE.principal_id = grantee_principal_id
JOIN ##database_principals GRANTOR ON GRANTOR.DBname = 'Master' AND GRANTOR.principal_id = grantor_principal_id
LEFT JOIN sys.columns COL ON COL.object_id = PERM.major_id AND COL.column_id = PERM.minor_id;

SET @code = ''

SELECT @code +=  'BEGIN TRY' + CHAR(10) +
       'INSERT INTO ##database_permissions
        SELECT ''' + name + ''' as DBname,
               [PERM].[class_desc],
               OBJECT_name([PERM].[major_id],' + CONVERT(nvarchar,database_id) + ') AS [Object name],
               [GRANTEE].[name] AS [Grantee name],
               [GRANTOR].[name] AS [Grantor name],
               [PERM].[permission_name],
               [state_desc],
               [COL].[name] AS [Column],
               CASE
                   WHEN [PERM].[major_id] < 0 THEN ''Yes''
                   ELSE ''No''
               END AS [IsSystem]
        FROM ' + QUOTEname(name) + '.sys.database_permissions PERM
        JOIN ##database_principals GRANTEE ON GRANTEE.DBname = ''' + name + ''' AND GRANTEE.principal_id = grantee_principal_id
        JOIN ##database_principals GRANTOR ON GRANTOR.DBname = ''' + name + ''' AND GRANTOR.principal_id = grantor_principal_id
        LEFT JOIN ' + QUOTEname(name) + '.sys.columns COL ON COL.object_id = PERM.major_id AND COL.column_id = PERM.minor_id;' + CHAR(10) +
        'END TRY' + CHAR(10) +
       'BEGIN CATCH PRINT ''Cannot Read ##database_permissions information from ' + name + '''; END CATCH' + CHAR(10)
FROM sys.databases
WHERE [name] <> 'Master'

EXEC sp_executesql @code

SELECT 'Master' as DBname,
       [Roles].[Role_ID],
       [Roles].[Role_name],
       [Roles].[Member_ID],
       [Principals].[name] AS [Member_name]
INTO ##database_roles
FROM
(
    SELECT [Principals].[name] AS [Role_name],
           [Roles].[role_principal_id] AS [Role_ID],
           [Roles].[member_principal_id] AS [Member_ID]
    FROM   sys.database_principals Principals
    INNER JOIN sys.database_role_members Roles
    ON Principals.principal_id = Roles.role_principal_id
) Roles
INNER JOIN sys.database_principals Principals
ON Roles.Member_ID = Principals.principal_id
ORDER BY [Member_name];

SET @code = ''

SELECT @code +=    'BEGIN TRY' + CHAR(10) +
                   'INSERT INTO ##database_roles
                   SELECT ''' + name +''' as DBname,
                   [Roles].[Role_ID],
                   [Roles].[Role_name],
                   [Roles].[Member_ID],
                   [Principals].[name] AS [Member_name]
            FROM
            (
                SELECT [Principals].[name] AS [Role_name],
                       [Roles].[role_principal_id] AS [Role_ID],
                       [Roles].[member_principal_id] AS [Member_ID]
                FROM   ' + QUOTEname(name) + '.sys.database_principals Principals
                INNER JOIN ' + QUOTEname(name) + '.sys.database_role_members Roles
                ON Principals.Principal_ID = Roles.role_principal_id
            ) Roles
            INNER JOIN ' + QUOTEname(name) + '.sys.database_principals Principals
            ON Roles.Member_ID = Principals.Principal_ID
            ORDER BY [Member_name];' + CHAR(10) +
                   'END TRY' + CHAR(10) +
                   'BEGIN CATCH PRINT ''Cannot Read database_roles information from ' + name + '''; END CATCH' + CHAR(10) + CHAR(10)
FROM sys.databases
WHERE [name] <> 'Master'

EXEC sp_executesql @code

SELECT DP.*, LEFT(DR.[Roles],LEN(DR.[Roles])-1) as [Roles]
FROM ##database_permissions DP
OUTER APPLY 
(
    SELECT R.Role_name + '; '
    FROM ##database_roles R
    WHERE R.DBname = DP.DBname COLLATE DATABASE_DEFAULT
    AND R.Member_name = DP.[Grantee name] COLLATE DATABASE_DEFAULT
    FOR XML PATH ('')
) DR ([Roles]);


DROP TABLE ##database_principals
DROP TABLE ##database_permissions
DROP TABLE ##database_roles

Database Roles

Similar to the above code, it loops in all the databases to get the role of each user

SET NOCOUNT ON;

DECLARE @code nvarchar(max) = ''

IF (OBJECT_ID('tempdb..##database_rolesB') IS NOT NULL)
    DROP TABLE  ##database_rolesB

SELECT 'Master' as DBName,
       [Roles].[Role_ID],
       [Roles].[Role_Name],
       [Roles].[Member_ID],
       [Principals].[name] AS [Member_Name]
INTO ##database_rolesB
FROM
(
    SELECT [Principals].[name] AS [Role_Name],
           [Roles].role_principal_id AS [Role_ID],
           [Roles].member_principal_id AS [Member_ID]
    FROM   sys.database_principals Principals
    INNER JOIN sys.database_role_members Roles
    ON Principals.principal_id = Roles.role_principal_id
) Roles
INNER JOIN sys.database_principals Principals
ON Roles.Member_ID = Principals.principal_id
ORDER BY [Member_Name];


SELECT @code +=    'BEGIN TRY' + CHAR(10) +
                   'INSERT INTO ##database_rolesB
                   SELECT ''' + name +''' as DBName,
                   [Roles].[Role_ID],
                   [Roles].[Role_Name],
                   [Roles].[Member_ID],
                   [Principals].[Name] AS [Member_Name]
            FROM
            (
                SELECT [Principals].[Name] AS [Role_Name],
                       [Roles].[role_principal_id] AS [Role_ID],
                       [Roles].[member_principal_id] AS [Member_ID]
                FROM   ' + QUOTENAME(name) + '.sys.database_principals Principals
                INNER JOIN ' + QUOTENAME(name) + '.sys.database_role_members Roles
                ON Principals.principal_id = Roles.role_principal_id
            ) Roles
            INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals Principals
            ON Roles.Member_ID = Principals.principal_id
            ORDER BY [Member_Name];' + CHAR(10) +
                   'END TRY' + CHAR(10) +
                   'BEGIN CATCH PRINT ''Cannot Read database_roles information from ' + name + '''; END CATCH' + CHAR(10) + CHAR(10)
FROM sys.databases
WHERE [name] <> 'Master'

PRINT @code

EXEC sp_executesql @code

SELECT * FROM ##database_rolesB

DROP TABLE ##database_rolesB