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
This is absolutely what I was looking for! A tweak needs to be made if you have Offline databases or this will cause an error. You must add this in the WHERE clause “state = 0” in all references “FROM sys.databases”. Thank you, thank you!