solving sql server problems for millions of dbas and developers since 2006

SQL Server Security Audit Report

By: Kun Lee | Read Comments (8) | Print

Kun is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

Related Tips: More

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:

Lists who has 'sa' permissions
List which accounts have local administrator access
Lists type of logins that are used
- Individual NT Login
- Individual SQL Login
- NT Group Login
If NT groups are being used a list of who is in the groups
Checks to see if any Windows accounts that are in SQL Server no longer exist
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'

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.

Individual NT Login
Individual SQL Login
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.

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.

Related Tips: More | Become a paid author

Last Update: 11/16/2009

Share:

Free SQL Server Learning

Comments and Feedback:

Monday, November 16, 2009 - 12:23:03 AM - K. Brian Kelley

Read The Tip

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.

Monday, November 16, 2009 - 8:20:08 AM - --cranfield	Read The Tip
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.

Tuesday, November 17, 2009 - 5:54:14 PM - Sadequl Hussain

Read The Tip

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

Friday, November 20, 2009 - 3:54:20 AM - ALZDBA

Read The Tip

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 ;

Monday, March 28, 2011 - 10:26:39 PM - jamie

Read The Tip

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

Monday, March 28, 2011 - 10:33:24 PM - K. Brian Kelley

Read The Tip

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.

Friday, May 27, 2011 - 8:57:58 AM - Lelo121

Read The Tip

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

Friday, May 27, 2011 - 9:02:27 AM - K. Brian Kelley	Read The Tip
Take a look at the sys.server_principals and sys.databases catalog views. Those are the replacements for syslogins and sysdatabases.

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool.

Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Join the over million SQL Server Professionals who get their issues resolved daily.

Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts