Audit nightmare - show all users with write access to a database

we have all been asked that age old question by an auditor to show all users that have write access to a database.

We all know its not an easy thing to show.

I think this script answers that question:-

select 'below shows users who have explicit insert, update, delete, alter, or control permissions:'

Select P.name As Principal,

       class_desc As PermissionLevel,    permission_name As PermissionGranted,

       ObjectName = Case class When 0 Then DB_NAME()

              When 1 Then OBJECT_SCHEMA_NAME(major_id) + N'.' + OBJECT_NAME(major_id)

              End

From sys.database_permissions As DP

Inner Join sys.database_principals As P On P.principal_id = DP.grantee_principal_id

Where permission_name In ('insert', 'update', 'delete', 'control', 'alter')

And state = 'G'; -- Grant

select 'below shows users who have built-in role membership that allows write operations:'

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? ;

Select ''?'' as databasename,  P1.name As Principal,

       P2.name As DBRole

From sys.database_principals As P1

Inner join sys.database_role_members As RM On RM.member_principal_id = P1.principal_id

Inner join sys.database_principals As P2 On P2.principal_id = RM.role_principal_id

Where P2.name in (''db_owner'', ''db_datawriter'', ''db_ddladmin'')

end'

CREATE TABLE #role (databasename varchar(400), principal varchar(400) , dbrole varchar (400))

INSERT INTO #role 

EXEC sp_MSforeachdb @command

SELECT * FROM #role

DROP table #role

select 'below shows users who have DBO on a database'

CREATE TABLE #rolemember (databasename varchar(400),  dbrole varchar (400))

SELECT @command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 

BEGIN USE ? ;

Select ''?'' as databasename, SUSER_SNAME(owner_sid)

From sys.databases

Where database_id = DB_ID();

end'

INSERT INTO #rolemember 

EXEC sp_MSforeachdb @command

SELECT * FROM  #rolemember 

DROP TABLE #rolemember 

select 'Below shows users who have role membership at the server level that gives them write access to the database:'

Select P1.name As Principal,

P2.name As ServerRole

From sys.server_principals As P1

Inner join sys.server_role_members As RM On RM.member_principal_id = P1.principal_id

Inner join sys.server_principals As P2 On P2.principal_id = RM.role_principal_id

Where P2.name = 'sysadmin';

select 'Below shows users who have permissions granted at the server level that gives them write access to the database:'

Select P.name As Principal,

class_desc As PermissionLevel,

permission_name As PermissionGranted

From sys.server_permissions As SP

Inner join sys.server_principals As P On P.principal_id = SP.grantee_principal_id

Where permission_name In ('control server', 'alter any database')

And state = 'G'; -- Grant