SOX security

This query can be used to show invalid user security permissions. you must manage your exclusions manually. use this as a template for creating a group of procedures to check your servers for sql security violations. I use this to generate reports for auditors or for checking my databases for changes against a known list of exclusions. Of course the easy way is to spend 269.99 on DIAB which has all the tools built in. DIAB is like having hundreds of your favorite scripts all pre-tested and ready to use. 

-- This job will run queries against the databases within a server. it will then compile all the results
-- into serveral temporary tables. The results are then pulled from these tables into two grids minus the
-- exclusions you may have selected.

declare @cmd1 as varchar(3000)

create table #dbuserroleaccess(dbase nvarchar(50),username nvarchar(100), groupname nvarchar(100), loginname nvarchar(100))
create table #objectpermissions(dbase nvarchar(50),object nvarchar(250),grantee nvarchar(100), grantor nvarchar(100),protecttype nvarchar(50),action nvarchar(50),tablecol nvarchar(100))

create Table #tempuserroleaccess(username nvarchar(100), groupname nvarchar(250),
                loginname nvarchar(250), defdbname nvarchar(250), DefSchemaName nvarchar(250), userid int, sid nvarchar(500))

create table #tempobjpermissions(owner nvarchar(50),object nvarchar(250),grantee nvarchar(100), grantor nvarchar(100),protecttype nvarchar(50),action nvarchar(50),tablecol nvarchar(100))

create table #tempsrvroleaccess(serverrole nvarchar(100),membername nvarchar(100), membersid nvarchar(250))

-- **** Insert into Tables
insert into #tempsrvroleaccess
exec sp_helpsrvrolemember

insert into #tempuserroleaccess
exec sp_helpuser

insert into #tempobjpermissions
exec sp_helprotect

-- **** Insert into summary Tables
insert into #dbuserroleaccess            -- unique code for master only to gather server level data
(dbase,username,groupname,loginname)
select 'ServerName',membername,serverrole,membername from #tempsrvroleaccess

insert into #dbuserroleaccess
(dbase,username,groupname, loginname)
select 'master',username, groupname, loginname from #tempuserroleaccess

insert into #objectpermissions
(dbase,object,grantee,grantor,protecttype,action, tablecol)
select 'master',object,grantee,grantor,protecttype,action,tablecol from #tempobjpermissions
drop table #tempuserroleaccess
drop table #tempobjpermissions
drop table #tempsrvroleaccess

-- ********** Operator Modification Modify the if statement below to include the names of the Databases you want to check *****
-- ********** This will loop through all databases and build two tables showing access to roles and access to objects *********
set @cmd1 = 'use [?]
if ''?'' in (''msdb'',''master'',''model'')
BEGIN
    create Table #tempuserroleaccess(username nvarchar(100), groupname nvarchar(250),
                loginname nvarchar(250), defdbname nvarchar(250), DefSchemaName nvarchar(250), userid int, sid nvarchar(500))

    create table #tempobjpermissions(owner nvarchar(50),object nvarchar(250),grantee nvarchar(100), grantor nvarchar(100),protecttype nvarchar(50),action nvarchar(50),tablecol nvarchar(100))

    insert into #tempuserroleaccess
    exec sp_helpuser

    insert into #tempobjpermissions
    exec sp_helprotect

    insert into #dbuserroleaccess
    (dbase,username,groupname, loginname)
    select ''?'',username, groupname, loginname from #tempuserroleaccess

    insert into #objectpermissions
    (dbase,object,grantee,grantor,protecttype,action, tablecol)
    select ''?'',object,grantee,grantor,protecttype,action,tablecol from #tempobjpermissions

    delete from #objectpermissions
   where grantee COLLATE DATABASE_DEFAULT in (select name COLLATE DATABASE_DEFAULT from sysusers where status = 32)

    drop table #tempuserroleaccess
    drop table #tempobjpermissions
END'

--*** Loop through all databases and gather data into #DBUserRoleAccess and #ObjectPermissions
exec sp_MSforeachdb @command1=@cmd1


-- *********** Part 1 The section Below is for Building and displaying Users who have access to Server and Database Roles *************
declare @count1 as int,
@count2 as int,
@SQL as nvarchar(500)

-- *** Operator Modification use the where clause below to exclude known safe users from the display at the database level
--  Build Database Exclusions from the list
select username + dbase + groupname as excluded
    into #exclusions
    from #dbuserroleaccess
    where username = 'Dummy' -- Dummy record do not remvome it's to avoid pulling everything if not excluding anything
    or ((username = 'anyvaliduser') and (dbase = 'model') and (groupname = 'db_owner'))
    or ((username = 'anyotheruser') and (dbase = 'master') and (groupname = 'sysadmin'))
   

    Select cast(dbase as varchar(20)) "DBase",cast(username as varchar(35)) "UserName",cast(groupname as varchar(20)) "GroupName",
                cast(loginname as varchar(35)) "LoginName"
    into ##dbroleaccess
    from #dbuserroleaccess
    where groupname in ('sysadmin','securityadmin','serveradmin','processadmin','bulkadmin','dbcreator','diskadmin','setupadmin',
                        'db_owner','db_accessadmin','db_ddladmin','db_securityadmin',
                        'db_datawriter') -- *** Operator Modification pick the database roles you want see who has access to
    and username not in('Administrator','sa') -- *** Operator Modification Exclude safe users at the server level
    and username + dbase + groupname not in (select excluded from #exclusions)
    order by dbase,username
   
-- Now display the Server & Database users who have access to roles
select DBase "Data Base", UserName "User" , GroupName "Role Permissions", LoginName "Login Name"
from ##dbroleaccess
 

-- ************** Part 2 The section below is for displaying Object level permissions *********************

-- *** Operator Modification use the where clause below to exclude known users/roles that have
-- *** object level permissions you do not wish to display
select grantee + dbase + object as excluded
into #exclusions2
From #objectpermissions
where ((grantee = 'public') and (dbase = 'master') and (object = 'dtproperties'))
    or ((grantee = 'public') and (dbase = 'TW_PROD') and (object = 'dtproperties'))
       
    select cast(A.dbase as varchar(15)) "Database", cast(A.grantee as varchar(30)) "Grantee", cast(A.object as varchar(30)) "Object", cast(A.protecttype as varchar(30)) "ProtectType", cast(A.action as varchar(30)) "Action", cast(A.tablecol as varchar(30)) "TableColumn", cast(A.grantor as varchar(30)) "Grantor"
    into ##objtemppermissions
    from #objectpermissions A
    where action in ('Insert','Update','Delete')
    and grantee not in('Administrator','sa','dbo')
    and A.grantee + A.dbase + A.object not in (select excluded from #exclusions2)
    union         
    select cast(A.dbase as varchar(15)) "Database", cast(A.grantee as varchar(30)) "Grantee", cast(A.object as varchar(30)) "Object", cast(A.protecttype as varchar(30)) "ProtectType", cast(A.action as varchar(30)) "Action", cast(A.tablecol as varchar(30)) "TableColumn", cast(A.grantor as varchar(30)) "Grantor"
    from #objectpermissions A
    where action in ('Insert','Update','Delete')
    and grantee not in('Administrator','sa','dbo')
    and A.grantee in ('guest','public')
    and A.grantee + A.dbase + A.object not in (select excluded from #exclusions2)

-- Show me the Users who have Insert/Update/Delete permissions on the tables
select [Database], Grantee, Object, ProtectType "Type Permission", Action, TableColumn "Table Column", Grantor
from ##objtemppermissions

drop table #dbuserroleaccess
drop table #objectpermissions
drop table ##objtemppermissions
drop table #exclusions
drop table ##dbroleaccess
drop table #exclusions2