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
