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 TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

2 Comments on “SQL Server Security Audit – A (time consuming) task for DBA simplified”

  1. Excellent script – thanks!
    One suggestion to avoid Offline/Restoring databases would be to add a Try/Catch:
    /*** jump over offline dbs ***/
    Begin TRY
    insert into #temp
    exec sp_executesql @cmd
    End TRY
    Begin CATCH
    GOTO Skipped_db
    End CATCH
    Skipped_db:
    set @cnt = @cnt+1
    /*** end jump over offline dbs ***/

Leave a Reply

Your email address will not be published. Required fields are marked *