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.
Kun is a database administrator in Washington, DC. His areas of interest are database administration, architecture, data modeling and development. He loves technologies of all kinds.
