SQL Server Security Audit – A (time consuming) task for DBA simplified

Many DBAs need to perform SQL Server security audit and save the permissions for each account at server level and database level. This will be done time to time and verified so that no elevated permissions are provided to any user than required. But to collect data of database roles and server roles of all the logins and users is a challenge. So I have written the below script by spending some time rather than manually doing it every quarter for dozens of servers. The below script will run on SQL Server versions above SQL 2005 and the script commented out will help for running on SQL 2000 servers.

For SQL Server 2005 and above

declare @name as nvarchar(max)
declare @cmd as nvarchar(max)
declare @cnt int
 
set @cnt = 1
 
create table #temp
(server_name nvarchar(max),
level nvarchar(max),
login_name sysname,
dbname nvarchar(max),
db_role sysname,
issysadmin bit,
issecurityadmin bit,
isserveradmin bit,
issetupadmin bit,
isprocessadmin bit,
isdiskadmin bit,
isdbcreator bit,
isbulkadmin bit)
 
set @name = (select top 1 name from sys.sysdatabases order by name)
 
while @name IS NOT NULL
begin
 
set @cmd = 'select @@servername as server_name,
case when (l.sysadmin = 1) then ''server'' else ''database'' end as level,
u1.name as login_name, '''+@name+''', u2.name as role_db,
l.sysadmin as issysadmin, l.securityadmin as issecurityadmin, l.serveradmin as isserveradmin,
l.setupadmin as issetupadmin, l.processadmin as isprocessadmin, l.diskadmin as isdiskadmin,
l.dbcreator as isdbcreator, l.bulkadmin as isbulkadmin
from ['+@name+'].sys.sysusers u1,
['+@name+'].sys.sysusers u2,
['+@name+'].sys.database_role_members p,
['+@name+'].sys.syslogins l
where u1.uid = p.member_principal_id and u2.uid = p.role_principal_id
and l.sid = u1.sid'
 
insert into #temp
exec sp_executesql @cmd
 
set @cnt = @cnt+1
 
set @name = (select top 1 name from sys.sysdatabases where name > @name order by name)
end
 
select * from #temp order by login_name
 
drop table #temp

For SQL Server 2000

declare @name as nvarchar(200)
declare @cmd as nvarchar(2000)
declare @cnt int
 
set @cnt = 1
 
create table #temp
(server_name nvarchar(15), 
level nvarchar(10), 
login_name sysname,
dbname nvarchar(200), 
db_role sysname, 
issysadmin bit, 
issecurityadmin bit, 
isserveradmin bit, 
issetupadmin bit, 
isprocessadmin bit, 
isdiskadmin bit, 
isdbcreator bit, 
isbulkadmin bit)
 
set @name = (select top 1 name from sysdatabases order by name)
 
while @name IS NOT NULL
begin
 
 
set @cmd = 'select @@servername as server_name,
case when (l.sysadmin = 1) then ''server'' else ''database'' end as level,
u1.name as login_name, '''+@name+''', u2.name as role_db,
l.sysadmin as issysadmin, l.securityadmin as issecurityadmin, l.serveradmin as isserveradmin, 
l.setupadmin as issetupadmin, l.processadmin as isprocessadmin, l.diskadmin as isdiskadmin, 
l.dbcreator as isdbcreator, l.bulkadmin as isbulkadmin
from ['+@name+']..sysusers u1, 
['+@name+']..sysusers u2, 
['+@name+']..sysmembers p,
master..syslogins l
where u1.uid = p.memberuid and u2.uid = p.groupuid
and l.sid = u1.sid'
 
insert into #temp
exec sp_executesql @cmd
 
set @cnt = @cnt+1
 
set @name = (select top 1 name from sysdatabases where name > @name order by name)
end
 
select * from #temp order by login_name
 
 
drop table #temp

The above script returns redundant data at server level but I can compromise with that given the filter options in the excel sheets.

Happy Troubleshooting,

Regards

Manohar Punna

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook