SQL Server Security Audit Report

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


Problem

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.

Solution

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.

Script

USE master
GO
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)

DECLARE srv_role CURSOR FAST_FORWARD FOR 
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END

CLOSE srv_role
DEALLOCATE srv_role

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' )
BEGIN
CREATE TABLE #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
END

DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole

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


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

DECLARE grp_role CURSOR FAST_FORWARD FOR 
SELECT REPLACE(name,'US\','') 
FROM syslogins 
WHERE isntgroup = 1 AND name LIKE 'US\%'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname

WHILE @@FETCH_STATUS = 0
BEGIN

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

PRINT @sqlcmd 
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd

SET ROWCOUNT 8
DELETE FROM #temp_groupadmin

SET ROWCOUNT 0

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
END


CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_groupadmin2

DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2



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


-- 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)

DECLARE grp_role CURSOR FOR 
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb') 
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' 


OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE #temp_rolemember 
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'

PRINT @sqlcmd2 
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)

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

FETCH NEXT FROM grp_role INTO @dbname
END


CLOSE grp_role
DEALLOCATE grp_role

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'

Securi1

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.

Securi2

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.

Securi3

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.

Securi4

 

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.

S1

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.

Securi5

Limitations

  • 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-11-16

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.

SELECT
    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
go
RECONFIGURE
GO
exec sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

 

-- 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

Naveen

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)

 

Thanks!!

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')
begin
        EXEC sys.sp_configure N'xp_cmdshell', N'1'
        RECONFIGURE WITH OVERRIDE
end

.... 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')
begin
        EXEC sys.sp_configure N'xp_cmdshell', N'0'
        RECONFIGURE WITH OVERRIDE
end

/* 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:

http://www.sql-server-performance.com/articles/audit/security_audit_server_level_p1.aspx
http://www.sql-server-performance.com/articles/dba/security_audit_database_level_p1.aspx
http://www.sql-server-performance.com/articles/audit/security_audit_operating_system_p1.aspx


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.


Thanks

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.

 














get free sql tips
agree to terms