List all sql and AD users with security settings (must be used for SOX evidence)

To qualify as evidence we must be able to provide the source code for how we are geting the evidence. When it comes to user rights and settings this code will do the job. It will list all users and their rights , both in every database and their server roles. I have rewritten some of the code from dbtracking to do the job. It take a few minutes to run the code sinc we need to check each database for a series of settings.


use master
go
SET nocount on
declare @UserName varchar(128)
declare @string varchar(500)
declare Sqlserverusers cursor fast_forward for
SELECT name FROM syslogins
open Sqlserverusers
fetch next from Sqlserverusers
into @UserName
while @@fetch_status = 0
begin
SELECT [name] as Username,dbname as DatabaseName,createdate,updatedate,isntgroup,isntuser,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,
diskadmin,dbcreator,bulkadmin FROM syslogins where [name] = @username
declare @LoginNamePattern sysname
set @LoginNamePattern = @UserName
Declare @exec_stmt nvarchar(3550)

Declare
@RetCode int
,@CountSkipPossUsers int
,@Int1 int

Declare
@c10DBName sysname
,@c10DBStatus int
,@c10DBSID varbinary(85)

Declare
@charMaxLenLoginName varchar(11)
,@charMaxLenDBName varchar(11)
,@charMaxLenUserName varchar(11)
,@charMaxLenLangName varchar(11)

Declare
@DBOptLoading int
,@DBOptPreRecovery int
,@DBOptRecovering int
,@DBOptSuspect int
,@DBOptOffline int
,@DBOptDBOUseOnly int
,@DBOptSingleUser int

CREATE Table #tb1_UA
(
LoginName sysname collate database_default NOT Null
,DBName sysname collate database_default NOT Null
,UserName sysname collate database_default NOT Null
,UserOrAlias char(8) collate database_default NOT Null
)


Select
@RetCode = 0
,@CountSkipPossUsers = 0


IF (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
Select @RetCode = 1
goto label_86return
end


SELECT @DBOptLoading = number
from master.dbo.spt_values
where type = 'D'
and name = 'loading'

SELECT @DBOptPreRecovery = number
from master.dbo.spt_values
where type = 'D'
and name = 'pre recovery'

SELECT @DBOptRecovering = number
from master.dbo.spt_values
where type = 'D'
and name = 'recovering'

SELECT @DBOptSuspect = number
from master.dbo.spt_values
where type = 'D'
and name = 'not recovered'

SELECT @DBOptOffline = number
from master.dbo.spt_values
where type = 'D'
and name = 'offline'

SELECT @DBOptDBOUseOnly = number
from master.dbo.spt_values
where type = 'D'
and name = 'dbo use only'

SELECT @DBOptSingleUser = number
from master.dbo.spt_values
where type = 'D'
and name = 'single user'


DECLARE ms_crs_10_DB
Cursor local static For
SELECT
name ,status ,sid
from
master.dbo.sysdatabases

OPEN ms_crs_10_DB

WHILE (10 = 10)
begin


FETCH
Next
from
ms_crs_10_DB
into
@c10DBName
,@c10DBStatus
,@c10DBSID


IF (@@fetch_status <> 0)
begin
Deallocate ms_crs_10_DB
BREAK
end


IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
AND @c10DBSID <> suser_sid()
)
begin
Select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end


IF (@c10DBStatus & @DBOptSingleUser > 0)
begin

SELECT @Int1 = count(*)
from master.dbo.sysprocesses
where spid <> @@spid
and dbid = db_id(@c10DBName)

IF (@Int1 > 0)
begin
Select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
end


IF (@c10DBStatus &
(
@DBOptLoading
| @DBOptRecovering
| @DBOptSuspect
| @DBOptPreRecovery
)
> 0
)
begin
Select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end


IF (@c10DBStatus &
(
@DBOptOffline
)
> 0
)
begin

CONTINUE
end

IF (has_dbaccess(@c10DBName) <> 1)
begin
raiserror(15622,-1,-1, @c10DBName)
CONTINUE
end


select @exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
u.sid = l.sid AND isaliased=0' +
case @LoginNamePattern
when null then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
Select

N' + quotename(@c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @LoginNamePattern
when null then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
end

EXECUTE(@exec_stmt)

end


SELECT
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
,isnull ( max(datalength(DBName)) ,6)
)
,@charMaxLenUserName =
convert ( varchar
,isnull ( max(datalength(UserName)) ,8)
)
from
#tb1_UA


EXECUTE(
'



SELECT
''LoginName'' = substring (LoginName ,1 ,'
+ @charMaxLenLoginName + ')

,''DBName'' = substring (DBName ,1 ,'
+ @charMaxLenDBName + ')

,''UserName'' = substring (UserName ,1 ,'
+ @charMaxLenUserName + ')

,UserOrAlias
from
#tb1_UA
order by
1 ,2 ,3
'
)
DROP Table #tb1_UA
label_86return:


IF (object_id('#tb1_UA') IS NOT Null)
DROP Table #tb1_UA

fetch next from Sqlserverusers
into @UserName
end
close Sqlserverusers
deallocate Sqlserverusers