Rotating Header Image

Server Role Audit – a quick and dirty script

Every now and again I like to run a quick audit of the SQL Servers I support and ensure that logins do not have any elevated permissions on a server. With properly auditing and tracking you should be able to identify any problems when they happen but it doesn't hurt to give the server(s) a clean sweep to make sure that nothing was missed or over looked. Below is a script that will list all the logins and cross tab that with the server roles within SQL Server. If a user is a member of a server role it will be marked with an “X”.

Hint: Using SQL Server 2008’s Central Management Servers will allow you to get this “snapshot” for all your servers at once

SELECT
@@SERVERNAME AS 'InstanceName', name AS Login,
sysadmin =
CASE
WHEN sysadmin = 1 THEN 'X'
ELSE ''
END,
securityadmin =
CASE
WHEN securityadmin = 1 THEN 'X'
ELSE ''
END,
serveradmin =
CASE
WHEN serveradmin = 1 THEN 'X'
ELSE ''
END,
setupadmin =
CASE
WHEN setupadmin = 1 THEN 'X'
ELSE ''
END,
processadmin =
CASE
WHEN processadmin = 1 THEN 'X'
ELSE ''
END,
diskadmin =
CASE
WHEN diskadmin = 1 THEN 'X'
ELSE ''
END,
dbcreator =
CASE
WHEN dbcreator = 1 THEN 'X'
ELSE ''
END,
bulkadmin =
CASE
WHEN bulkadmin = 1 THEN 'X'
ELSE ''
END,
CONVERT(CHAR(16),createdate,121) AS 'DateCreated'
FROM MASTER.dbo.syslogins
ORDER BY NAME

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.