SQL Security Audit Report

query-users.sql

/*
Found the base of this query on StackOverflow, I've gone through and cleaned some things up and given some options

Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

-- Specifiy a specfici user, leave blank for all 
DECLARE @DatabaseUserName varchar(512) = ''
DECLARE @PermissionType varchar(512) = ''
DECLARE @PermissionTypeNot varchar(512) = ''

--List all access provisioned to a sql user or windows user/group directly 
SELECT 
	R.* 
	,(CASE 
		WHEN R.PermissionType  IS NULL OR R.PermissionState IS NULL OR R.ObjectName IS NULL THEN '-- No Action --'
		WHEN R.DatabaseUserName IN ('dbo', '{All Users}') THEN '-- No Action --'
		ELSE 
			UPPER(R.PermissionState) COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + UPPER(R.PermissionType) COLLATE SQL_Latin1_General_CP1_CI_AS + ' ON [' + DB_NAME() + '].[' + R.[ObjectSchema] + '].[' + R.ObjectName COLLATE SQL_Latin1_General_CP1_CI_AS + '] TO [' + R.DatabaseUserName COLLATE SQL_Latin1_General_CP1_CI_AS + ']; GO;'
	END) As [SqlStatement]
FROM (

	SELECT TOP 100 PERCENT 
    [UserName] = (CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END)
	,[UserType] = (CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END)
	,[DatabaseUserName] = princ.[name]
	,[Role] = null
	,[PermissionType] = perm.[permission_name]
	,[PermissionState] = perm.[state_desc]
	,[ObjectType] = obj.[type_desc] 
	,[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id)
	,[ObjectName] = OBJECT_NAME(perm.major_id)
	,[ColumnName] = col.[name]
FROM sys.database_principals princ																	-- database user
LEFT JOIN sys.login_token ulogin on princ.[sid] = ulogin.[sid]										--Login accounts
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]		--Permissions
LEFT JOIN sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id]	--Table columns
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')

UNION	-- List all access provisioned to a sql user or windows user/group through a database or application role

SELECT TOP 100 PERCENT 
    [UserName] = (CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END)
	,[UserType] = (CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END)
	,[DatabaseUserName] = memberprinc.[name]
	,[Role] = roleprinc.[name]
	,[PermissionType] = perm.[permission_name]
	,[PermissionState] = perm.[state_desc]
	,[ObjectType] = obj.[type_desc]
	,[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id)
	,[ObjectName] = OBJECT_NAME(perm.major_id)
	,[ColumnName] = col.[name]
FROM sys.database_role_members members																	--Role/member associations
JOIN sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]		--Roles
JOIN sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]	--Role members (database users)
LEFT JOIN sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]									--Login accounts
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]		--Permissions
LEFT JOIN sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id]		--Table columns
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]

UNION	--List all access provisioned to the public role, which everyone gets by default

SELECT TOP 100 PERCENT 
    [UserName] = '{All Users}'
	,[UserType] = '{All Users}'
	,[DatabaseUserName] = '{All Users}'
	,[Role] = roleprinc.[name]
	,[PermissionType] = perm.[permission_name]
	,[PermissionState] = perm.[state_desc]
	,[ObjectType] = obj.[type_desc]
	,[ObjectSchema] = OBJECT_SCHEMA_NAME(perm.major_id)
    ,[ObjectName] = OBJECT_NAME(perm.major_id)
    ,[ColumnName] = col.[name]
FROM  sys.database_principals roleprinc																	-- Roles
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]		-- Role permissions
LEFT JOIN sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id]		-- Table columns
JOIN sys.objects obj ON obj.[object_id] = perm.[major_id]												-- All objects   
WHERE
    roleprinc.[type] = 'R'		-- Only roles
	AND
    roleprinc.[name] = 'public' -- Only public role
	AND
    obj.is_ms_shipped = 0		-- Only objects of ours, not the MS objects

) As R 
WHERE 
	(@DatabaseUserName IS NULL OR @DatabaseUserName = '' OR R.DatabaseUserName = @DatabaseUserName) 
	AND 
	(@PermissionType IS NULL OR @PermissionType = '' OR R.PermissionType = @PermissionType) 
	AND 
	(@PermissionTypeNot IS NULL OR @PermissionTypeNot = '' OR R.PermissionType <> @PermissionType) 

ORDER BY 
	(CASE WHEN R.[UserName] IS NULL THEN 1 ELSE 0 END)

/*

ORDER BY
    princ.[Name]
	,OBJECT_NAME(perm.major_id)
	,col.[name]
	,perm.[permission_name]
	,perm.[state_desc]
	,obj.type_desc--perm.[class_desc] 

*/