Server Permissions Audit

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: Jim Sebastiano

Date: 7 May 2010

Description

Lists all Server and Database roles on the server, along with their member logins and users

Code

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

*** Server Permissions Audit ***

**************************************************************************************************

This script is used for auditing the permissions that exist on a SQL Server. It will scan every

database on the server (separate scripts to run only one database are commented at the bottom)

and return four record sets:

1. Audit who is in server-level roles

2. Audit roles on each database, defining what they are and what they can do

3. Audit the roles that users are in

4. Audit any users that have access to specific objects outside of a role

NOTE: This script was written for MS SQL Server 2005 and uses undocumented system tables, rather

than the standard MS procedures. It is likely that this script will not work in future versions

of SQL Server.

Created: 2010-05-07

Jim Sebastiano

NOTE: ChillyDBA - Tested on SQL 2017, so it's pretty safe to say it works on everything in between

*/

DECLARE @ShowOnlyThisLogin VARCHAR(50)

SET @ShowOnlyThisLogin = NULL -- leave null for all IDs, otherwise 'SomeLogin'

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

SET NOCOUNT ON

DECLARE @currDB VARCHAR(100), @sql varchar(2000)

DECLARE @databases TABLE (dbname VARCHAR(100))

INSERT INTO @databases (dbname)

SELECT [Name]

FROM master.sys.databases

WHERE state_desc <> 'OFFLINE'

CREATE TABLE #AuditServerRoles

(ServerName VARCHAR(100), DatabaseName VARCHAR(100), ServerRole VARCHAR(100), MemberName VARCHAR(100))

CREATE TABLE #AuditDatabaseRoles

(ServerName VARCHAR(100), DatabaseName VARCHAR(100), RoleName VARCHAR(100), SchemaName VARCHAR(100),

ObjectName VARCHAR(100), PermissionType VARCHAR(100), StateDesc VARCHAR(100), Grantor VARCHAR(100))

CREATE TABLE #AuditDatabaseRoleAssignments

(ServerName VARCHAR(100), DatabaseName VARCHAR(100), RoleName VARCHAR(100), UserName VARCHAR(100))

CREATE TABLE #AuditUserLevelAssignments

(ServerName VARCHAR(100), DatabaseName VARCHAR(100), SchemaName VARCHAR(100), ObjectName VARCHAR(100),

ObjectType VARCHAR(100), Grantee VARCHAR(100), Grantor VARCHAR(100),

UserType VARCHAR(100), PermissionType VARCHAR(100), PermissionState VARCHAR(100))

-- Step 1: Audit who is in server-level roles

INSERT INTO #AuditServerRoles

SELECT

@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,

SUSER_NAME(rm.role_principal_id) AS ServerRole, lgn.name AS MemberName

FROM

sys.server_role_members rm

INNER JOIN sys.server_principals lgn

ON rm.role_principal_id >=3 AND rm.role_principal_id <=10

AND rm.member_principal_id = lgn.principal_id

ORDER BY 1, 2, 3, 4

-- loop through all databases

while exists (select * from @databases)

begin

set @currDB = (select top 1 dbname from @databases order by dbname)

PRINT @currdb

-- Step 2: Audit roles on each database, defining what they are, what they can do, and who belongs in them

INSERT INTO #AuditDatabaseRoles

exec ('use ' + @currdb + ';

SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, dprin.name AS RoleName,

ISNULL(sch.name, osch.name) AS SchemaName, ISNULL(o.name, ''.'') AS ObjectName,

dperm.permission_name, dperm.state_desc, grantor.name AS Grantor

FROM

sys.database_permissions dperm

INNER JOIN sys.database_principals dprin

ON dperm.grantee_principal_id = dprin.principal_id

INNER JOIN sys.database_principals grantor

ON dperm.grantor_principal_id = grantor.principal_id

LEFT OUTER JOIN sys.schemas sch

ON dperm.major_id = sch.schema_id AND dperm.class = 3

LEFT OUTER JOIN sys.all_objects o

ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1

LEFT OUTER JOIN sys.schemas osch

ON o.schema_id = osch.schema_id

WHERE dprin.name <> ''public'' AND dperm.type <> ''CO'' AND dprin.type = ''R''

ORDER BY 1, 2, 3, 4, 5, 6')

-- Step 3: Audit the roles that users are in

INSERT INTO #AuditDatabaseRoleAssignments

exec ('use ' + @currdb + ';

SELECT

@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,

CASE WHEN (r.principal_id IS NULL) THEN ''public''

ELSE r.name

END AS RoleName,

u.name AS UserName

FROM

sys.database_principals u

LEFT JOIN (sys.database_role_members m JOIN sys.database_principals r ON m.role_principal_id = r.principal_id)

ON m.member_principal_id = u.principal_id

ORDER BY 1, 2, 3, 4')

-- Step 4: Audit any users that have access to specific objects outside of a role

INSERT INTO #AuditUserLevelAssignments

exec ('use ' + @currdb + ';

SELECT

@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,

ISNULL(sch.name, osch.name) AS SchemaName, ISNULL(o.name, ''.'') AS ObjectName,

o.type_desc,

dprin.NAME AS Grantee,

grantor.name AS Grantor,

dprin.type_desc AS principal_type_desc,

dperm.permission_name,

dperm.state_desc AS permission_state_desc

FROM

sys.database_permissions dperm

INNER JOIN sys.database_principals dprin

ON dperm.grantee_principal_id = dprin.principal_id

INNER JOIN sys.database_principals grantor

ON dperm.grantor_principal_id = grantor.principal_id

LEFT OUTER JOIN sys.schemas sch

ON dperm.major_id = sch.schema_id AND dperm.class = 3

LEFT OUTER JOIN sys.all_objects o

ON dperm.major_id = o.OBJECT_ID AND dperm.class = 1

LEFT OUTER JOIN sys.schemas osch

ON o.schema_id = osch.schema_id

WHERE dprin.name <> ''public'' AND dperm.type <> ''CO'' AND dprin.type <> ''R''

ORDER BY 1, 2, 3, 4, 5')

DELETE FROM @databases WHERE dbname = @currDB

END

IF @ShowOnlyThisLogin IS NULL

BEGIN

SELECT 'Server Roles', * FROM #AuditServerRoles ORDER BY 1,2,3,4,5

SELECT 'Database Roles', * FROM #AuditDatabaseRoles ORDER BY 1,2,3,4,5,6,7

SELECT 'DB Role Assignments', * FROM #AuditDatabaseRoleAssignments ORDER BY 1,2,3,4,5

SELECT 'User Level Assignments', * FROM #AuditUserLevelAssignments ORDER BY 1,2,3,4,5,6

END ELSE BEGIN

SELECT 'Server Roles', * FROM #AuditServerRoles WHERE MemberName = @ShowOnlyThisLogin ORDER BY 1,2,3,4,5

SELECT 'DB Role Assignments', * FROM #AuditDatabaseRoleAssignments WHERE UserName = @ShowOnlyThisLogin ORDER BY 1,2,3,4,5

SELECT 'User Level Assignments', * FROM #AuditUserLevelAssignments WHERE Grantee = @ShowOnlyThisLogin ORDER BY 1,2,3,4,5,6

END

DROP TABLE #AuditServerRoles, #AuditDatabaseRoles, #AuditDatabaseRoleAssignments, #AuditUserLevelAssignments