SQL Server Security Audit Report

By:   |   Updated: 2009-11-16   |   Comments (12)   |   Related: More > Auditing and Compliance

SQL Server Data Security and Compliance

Free MSSQLTips Webinar: SQL Server Data Security and Compliance

In this webinar, learn about security and compliance and things that you can implement with SQL Server to help keep your data secure.


If your company needs to go through a SOX (Sarbanes-Oxley) audit or any security audit, the DBA has to provide security information to them. If you have purchased third party tools to provide this information that is great. If you don't have third party tools and need to go through many servers to provide this information it can be a hassle and very time consuming. So I put together a script to generate a report that I could just review. The script generates a report of all elevated level accounts and any possible security holes.


The script that I created does the following:

  1. Lists who has 'sa' permissions
  2. List which accounts have local administrator access
  3. Lists type of logins that are used
    • Individual NT Login
    • Individual SQL Login
    • NT Group Login
  4. If NT groups are being used a list of who is in the groups
  5. Checks to see if any Windows accounts that are in SQL Server no longer exist
  6. The last part shows if any user is using database roles and which roles

With a combination of Windows system commands and T-SQL, you can pull this information easily. This script works for SQL 2000, SQL 2005 and SQL 2008.


USE master
SET nocount ON

-- Get all roles
CREATE TABLE #temp_srvrole 
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole

-- sp_help syslogins
CREATE TABLE #temp_memberrole 
(ServerRole VARCHAR(128), 
MemberName VARCHAR(265), 
MemberSID VARCHAR(300))

DECLARE @ServerRole VARCHAR(128)

ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole

EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole


SELECT ServerRole, MemberName FROM #temp_memberrole

-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole 
WHERE MemberName = 'BUILTIN\Administrators' 
AND ServerRole = 'sysadmin' )
#temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'

SELECT output AS local_administrator 
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin

DROP TABLE #temp_memberrole

-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname =
name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname =
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup =

-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin 
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)

FROM syslogins 
WHERE isntgroup = 1 AND name LIKE 'US\%'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname


@sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin

PRINT @sqlcmd 
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd

DELETE FROM #temp_groupadmin


INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')

FETCH NEXT FROM grp_role INTO @grpname


SELECT * FROM #temp_groupadmin2

DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2

PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins

-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember 
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final 
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))

DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)

name FROM sysdatabases
WHERE name NOT IN ('tempdb'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname


SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'

PRINT @sqlcmd2 
INSERT INTO #temp_rolemember

INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember

FETCH NEXT FROM grp_role INTO @dbname


SELECT * FROM #temp_rolemember_final

DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final

Script explanation

Step 1 - Lists who has 'sa' permissions

First, I used "sp_helpsrvrole" to pull all sever role and put on temp table.

Second, I had to find out if any login/group belongs to that group and if so, run "sp_helpsrvrolemember" to get the list of logins/groups for that role and in the sample result, you will see the below members belong to 'sysadmin'

Step 2 - List which accounts have local administrator access

Next, since I saw "BUILTIN\Administrators" in the the first list, I needed to find out who is in Local Administrator group for that server. I used the system command "net localgroup administrators" and xp_cmdshell to pull this information.

Step 3 -Lists type of logins that are used

Now I pull all the logins and also identify if it is in one of three categories as below.

  1. Individual NT Login
  2. Individual SQL Login
  3. NT Group Login

And result will be looks like this.

Step 4 - If NT groups are being used a list of who is in the groups

After that, since US\Database Administrators is an NT group login and we are using a lot of NT group logins, I needed to know who is in these groups. So, again I used the Windows command "net group" with xp_cmdshell (You can also use SQLCMD or PowerShell to pull the information). Note - I hid the actual names for security reasons.


Step 5 -Checks to see if any Windows accounts that are in SQL Server no longer exist

In this step I used sp_validatelogins.  This reports information about Windows users and groups that are mapped to SQL Server principals, but no longer exist in the Windows environment. So that you can clean up your logins.

Step 6 - The last part shows if any user is using database roles and which roles

For this step I wanted to know who is in particular roles like db_owner, data_reader or any other customized role by using the "sp_helprolemember" stored procedure.


  • If you don't have Active Directory on DMZ and the SQL server is in the DMZ, you may not get all the results due to limitations.
  • If your NT group contains another NT group, you will need to use the "dsquery group" command to get recursive results.
Next Steps
  • Run this script as is in your environment to see if you have any potential security holes
  • You can use Reporting Services or Excel with the data connection feature to make this a permanent report to review.
  • You can find more information about security, you can visit "SQL Server Security Tips" for best practice and others feature.

Last Updated: 2009-11-16

get scripts

next tip button

About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips
Related Resources

Comments For This Article

Thursday, May 16, 2019 - 8:38:25 AM - Perla Back To Top (80102)

Here is a query to get similar information.

    USER_NAME(grantee_principal_id) AS 'User'
  , state_desc AS 'Permission'
  , permission_name AS 'Action'
  , CASE class
      WHEN 0 THEN 'Database::' + DB_NAME()
      WHEN 1 THEN OBJECT_NAME(major_id)
      WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable'
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0; 

Wednesday, March 20, 2013 - 2:47:11 PM - Kun Lee Back To Top (22915)

Yap, this should fix it. Good luck!


-- Start code

use master

exec sp_configure 'show advanced options', 1
exec sp_configure 'xp_cmdshell', 1


-- end Code

Wednesday, March 20, 2013 - 9:19:43 AM - Bethany Dunlap Back To Top (22900)

I was thinking I needed to activate and deactivate cmdshell because I got an error saying:

"SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. "


Once I put in the code posted above from ALZDBA, I reran the script and got this error:

"The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option"


I looked up sp_configure but I am new to SQL and am not sure what I need to do. Any help would be appreciated. Thanks!




Monday, September 10, 2012 - 8:09:03 PM - Naveen Roperia Back To Top (19459)

Nice Article Lee,

Thank. I have a question: I have a SQL server outside the windows domain. A test user login has been created to access any table inside a database. Now multiple users have the access to this login and I need to track who logged in and when (*Instead of machine Name/IpAddress, I need to know which user from windows domain accessed the DB).

Problem: How do I capture the domain\login information although the SQL server is outside domain ?

(*Note: user first has to login/remote login to inside the domain and then access the server)



Thank You


Friday, May 27, 2011 - 9:02:27 AM - K. Brian Kelley Back To Top (13923)

Take a look at the sys.server_principals and sys.databases catalog views. Those are the replacements for syslogins and sysdatabases.

Friday, May 27, 2011 - 8:57:58 AM - Lelo121 Back To Top (13922)

I was under the assumption that the syslogins and sysdatabases tables were not going to be available in newer versions of SQL Server and were used in SQL Server 2000. If I wanted to make this work only for SQL Server 2005 and SQL Server 2008, what instructions should I change?

This is what I found in the MSDN website:

"This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

http://msdn.microsoft.com/en-us/library/ms187997(SQL.90).aspx (Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views)



Monday, March 28, 2011 - 10:33:24 PM - K. Brian Kelley Back To Top (13363)

No. I'm not the original author of the tip, but basically the script is matching for a domain of "US" and then removing the domain portion, leaving just the group name.


Monday, March 28, 2011 - 10:26:39 PM - jamie Back To Top (13362)

Sorry if this is a newbie question, but when your script says:

ms sql name LIKE 'US\%'

...are you actually just trying to match sqlnames that say "user?"

Friday, November 20, 2009 - 3:54:20 AM - ALZDBA Back To Top (4475)

Nice overview !

Keep in mind you may need to enable (and disable) xp_cmdshell configuration !

/* ALZDBA - Check if cmdshell needs to be activated */
Create table #CmdShellSettingBefore (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))

insert into #CmdShellSettingBefore
   EXEC sys.sp_configure N'xp_cmdshell'

if exists(select * from #CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
        EXEC sys.sp_configure N'xp_cmdshell', N'1'

.... Your cmdshell code overhere ....

/* ALZDBA - disable cmdshell if it wasn't active at the beginning of this proc */

if exists(select * from #CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
        EXEC sys.sp_configure N'xp_cmdshell', N'0'

/* clean up */
drop table #CmdShellSettingBefore ;

Tuesday, November 17, 2009 - 5:54:14 PM - Sadequl Hussain Back To Top (4449)

Hi Kun,

Great article. It really is helpful when you an automate the auting mechanism through scripts, jobs and reports. 

I had written something on the same topic (security audit) some time back in another SQL site, check these if you are interested:


Also, the free e-book "SQL Server Tacklebox" by Rodney Landrum - available from the SQL Server Central (http://www.sqlservercentral.com/Books/) has a great chapter on SQL Server Security Auditing.


Sadequl Hussain

Monday, November 16, 2009 - 8:20:08 AM - --cranfield Back To Top (4434)

Great tip. I like the way you put everything together.  I will have to update your code to work with multiple domains but its just what I need to provide the SOx auditors with the data they're always requesting.

Monday, November 16, 2009 - 12:23:03 AM - K. Brian Kelley Back To Top (4432)

You also need to audit for CONTROL SERVER rights by querying sys.server_permissions in SQL Server 2005/2008. That gives the same rights (with the exception of being able to bypass permissions) as being a member of the sysadmin fixed server role.



Recommended Reading

Auditing your SQL Server database and server permissions

SQL Server Login Properties to Enforce Password Policies and Expiration

Auditing Failed Logins in SQL Server

SQL Server Security Audit (Part 2): Scripts to help you or where can you find more information

Identify SQL Server databases that are no longer in use

get free sql tips
agree to terms