Auditing Your SQL Server Environment Part II Auditing Your SQL Server

Over the last few years I have been a roving SQL Server DBA contractor and

tended to work contacts in small and mid-size companies that involved

organizing, documenting and then tuning/optimizing existing SQL Servers in

those companies. I have noticed one very disturbing truth over the years; no

one seems to document anything. In fact, I was usually thankful if I could

find something or someone who knew the sa passwords on each SQL Server

installation, let alone knew anything else about their setups. I have often

been asked how I could go into a company that had dozens of servers, over 50

SQL Server databases, no existing documentation, no full-time DBA on staff,

and no documentation and ramp up to a functioning level in a very short time

frame. My answer was practice and my file of stored procedures w ritten over

the years that I carried with me which allowed me to do quick audits of the

SQL Server installations and databases so I could quickly produce

documentation to work from. This article is the second article in a series

that I intend to post to share those stored procedures and methods I have

learned with you to help you produce and learn a new environment if you move

on or obtain another project at your existing company.

Overview of SQL Server Roles

In response to reader requests I'm going to go into a little background on SQL

Server roles before I start describing the auditing process. SQL Server roles,

either fixed or user-defined, are SQL Server's answer to Windows groups and

basically serve the same purpose. Roles are a way to collect a batch of users

and define the same set of permissions for that batch of users without

granting those permissions to each individual user. Having said that remember

that if you get into the habit of granting permissions to roles rather than

individual SQL Server logins you will have a much easier maintaining

permissions in a dynamic environment.

SQL Server ships with a selection of fixed server and fixed database roles

that should be at the top of your list to examine when you decide to grant

permissions to roles rather than individual logins.

Fixed Server Roles

System Administrators(sysadmin)	This role is all encompassing and can do anything in SQL Server without setting any object permission. Use of this role should be highly protected and not assigned to logins without extreme justification.
Server Administrators(serveradmin)	This role is used to set server-wide configuration options and shut down servers. The logins assigned to this role also have the ability to add other logins to this role.
Setup Administrators(setupdamin)	This role can manage lined servers and all startup procedures. The logins assigned to this role also have the ability to add other logins to this role.
Security Administrators(securityadmin)	This role can manage logins and CREATE DATABASE permission, read error logs and change passwords. The logins assigned to this role also have the ability to add other logins to this role.
Process Administrators(processadmin)	This role can manage processes running in SQL Server. The logins assigned to this role also have the ability to add other logins to this role.
Database Creators(dbcreator)	This role can create, alter and drop databases. The logins assigned to this role also have the ability to add other logins to this role.
Disk Administrators(diskadmin)	This role can manage disk files. The logins assigned to this role also have the ability to add other logins to this role.
Bulk Administrators(bulkadmin)	This role can execute BULK INSERT statements. The logins assigned to this role also have the ability to add other logins to this role.

Fixed Database Roles

Database Owner(db_owner)	This database role is all encompassing and has all permissions in the database.
Database Access Administrators(db_accessadmin)	This role can add or remove Windows groups or SQL Server users to the database.
Database Security Administrators(db_securityadmin)	This role can manage all permissions, object ownership, roles and role memberships.
Database DDL Administrators(db_ddladmin)	This role can execute all data definition language (ddl) statements but cannot issue GRANT, REVOKE or DENY statements.
Database Backup Operators(db_backupoperator)	This role can execute DBCC, CHECKPOINT, and BACKUP statements.
Database Data Reader(db_datareader)	This role can SELECT any data in any user table in the database.
Database Data Writer(db_datawriter)	This role can INSERT, UPDATE, or DELETE any data in any user table in the database.
Database Deny Data Reader(db_denydatareader)	This role cannot SELECT any data in any user table in the database.
Database Deny Data Writer(db_denydatawriter)	This role cannot INSERT, UPDATE, or DELETE any data in any user table in the database.
Public(public)	This is a special role in which every database user belongs and is primarily used by SQL Server to give permissions to users to use objects needed by SQL Server to authenticate the user, let the user use Enterprise Manager, etc.

Roles and Replication

sysadmin

This role can enable, modify, or drop distributors, publishers, and

subscribers; create, drop, or modify a publication and its properties;

create or delete a push or pull subscription; update a PAL (publication

access list); enable snapshots for FTP downloading using the Internet;

configure agent profiles; monitor replication agents; configure agent

profiles; cleanup replication tasks; and schedule replication jobs.

db_owner

This role can create or drop, create or modify a publication or its

properties; create or delete a pull or push subscription; update a PAL;

enable snapshots for FTP downloading using the Internet; cleanup

replication tasks; and schedule replication jobs.

Managing SQL Server Fixed Roles

Several objects exist to aid in the management of fixed SQL Server roles.

While you cannot drop fixed roles or drop default role permissions, you can

add and delete role members (except for the public database role) and view the

permissions assigned to the roles.

Fixed Server Roles

sp_addsrvrolemember	System stored procedure that adds a login as a member of a fixed server role. Permissions default to members of the sysadmin server role that can add members to any fixed server role and members of a fixed server role to add members only to the same fixed server role.
sp_dropsrvrolemember	System stored procedure that removes a SQL Server login or a Windows NT user or group from a fixed server role. Permissions default to members of sysadmin fixed server role to remove members of any server role and members each server role that can remove other members of the same server role. Permissions are not transferable.
sp_helpsrvrolemember	System stored procedure that returns information about the members of a SQL Server fixed server role.
sp_srvrolepermission	System stored procedure that returns the permissions applied to a fixed server role.
sp_helpsrvrole	System stored procedure that returns a list of the SQL Server fixed server roles.
IS_SRVROLEMEMBER	Security function that returns an integer indicating whether the current user login is a member of the specified server role.

Fixed Database Roles

sp_addrolemember	System stored procedure that adds a security account as a member of an existing SQL Server database role in the current database. Permissions default to members of the sysadmin server role and the db_owner database role to add members to fixed database roles or user-defined roles. Owners of user-defined roles can add members to the roles they own and members of the db_securityadmin database role can add users to any user-defined role.
sp_droprolemember	System stored procedure that removes a security account from a SQL Server role in the current database. Permissions default to members of the sysadmin server role and the db_owner and db_securityadmin database and are not transferable and only a member of the sysadmin> fixed server role or the db_owner fixed database role can remove users from a fixed database role.
sp_dbfixedrolepermission	System stored procedure that displays the permissions for each fixed database role.
sp_helpdbfixedrole	System stored procedure that returns a list of the fixed database roles.
sp_helprole	System stored procedure that returns information about the roles in the current database.
IS_MEMBER	Security function that returns an integer indicating whether the current user is a member of the specified NT group or SQL Server role.

Managing User-Defined SQL Server Roles

SQL Server gives administrators the ability to create their own roles so they

can batch logins and define object permissions according to their project

needs. Often when you inherit a SQL Server installation and project you will

find a mixture of individual SQL Server logins and roles. Managing this

mixture can often become a daunting task. SQL Server provides you with a group

of system stored procedures and functions that can help you with this task.

sp_addrole	System stored procedure that creates a new SQL Server role in the current database. Permissions default to members of the sysadmin server role, and the db_securityadmin and db_owner database roles and are not transferable.
sp_droprole	System stored procedure that removes a SQL Server role from the current database. Permissions default to members of the sysadmin server role, the db_owner and db_securityadmin database roles, or the owner of the role and are not transferable.
sp_addrolemember	System stored procedure that adds a security account as a member of an existing SQL Server database role in the current database. Permissions default to members of the sysadmin server role and the db_owner database role to add members to fixed database roles or user-defined roles. Owners of user-defined roles can add members to the roles they own and members of the db_securityadmin database role can add users to any user-defined role.
sp_droprolemember	System stored procedure that removes a security account from a SQL Server role in the current database. Permissions default to members of the sysadmin server role and the db_owner and db_securityadmin database and are not transferable and only a member of the sysadmin fixed server role or the db_owner fixed database role can remove users from a fixed database role.
sp_helprole	System stored procedure that returns information about the roles in the current database.
sp_helpuser	System stored procedure that reports information about SQL Server users, Windows NT users, and database roles in the current database.
IS_MEMBER	Security function that returns an integer indicating whether the current user is a member of the specified NT group or SQL Server role.

Application Roles

Application roles are roles that differ from regular roles in that they have

passwords and do not have logins assigned to them. These roles allow a login

to connect to the database and assume the permissions granted to the

application role in place of their own. SQL Server has several system stored

procedures which allow you to create and manage application roles.

sp_addapprole	System stored procedure that adds a special type of role in the current database used for application security. Permissions default to members of the sysadmin server role, and the db_owner and db_securityadmin database roles
sp_dropapprole	System stored procedure that removes an application role from the current database. Permissions default to members of the sysadmin server role, the db_securityadmin and db_owner database roles and are not transferable.
sp_approlepassword	System stored procedure that changes the password of an application role in the current database. Permissions default to members of the sysadmin server role, and the db_securityadmin and db_owner database roles and are not transferable.
sp_setapprole	System stored procedure that activates the permissions associated with an application role in the current database.

Putting it all together

Now that we have briefly discussed the SQL Server objects that have been

created to manage roles it is an easy task to build a stored procedure to

audit your existing environment.

--Use the master database
USE master
go
IF OBJECT_ID('dbo.spRoleMembers') IS NOT NULL
DROP PROCEDURE dbo.spRoleMembers
GO
CREATE PROCEDURE dbo.spRoleMembers
AS
/**************************************************************************** 
   Creation Date: 04/28/02      Created By: Randy Dyess
                              Web Site: www.TransactSQL.Com
                              Email: RandyDyess@TransactSQL.Com
   Purpose: Loops through all databases and obtains members for database roles
   as well as server role members
   Location: master database
   Output Parameters: None
   Return Status: None
   Called By: None        
   Calls: None
   Data Modifications: None
   Updates: None                                                                
   Date        Author                      Purpose                                    
   ----------  --------------------------  ---------------------------------  
****************************************************************************/ 
SET NOCOUNT ON
--Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)
--Temp table to hold database and user-defiine role user names
CREATE TABLE #tRolemember
(
strServerName VARCHAR(50) DEFAULT @@SERVERNAME
,strDBName VARCHAR(50) 
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
--Temp table to hold database names
CREATE TABLE #tDBNames
(lngID INTEGER IDENTITY(1,1)
,strDBName VARCHAR(50)
)
--Create permanent table
IF OBJECT_ID ('dbo.tRolemember') IS NULL
BEGIN
CREATE TABLE dbo.tRolemember
(
strServerName VARCHAR(50) 
,strDBName VARCHAR(50) 
,strRoleName VARCHAR(50)
,strUserName VARCHAR(50)
,strUserID VARCHAR(100)
)
END
--Obtain members of each server role
INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC dbo.sp_helpsrvrolemember
--Obtain database names
INSERT INTO #tDBNames (strDBName)
SELECT name FROM master.dbo.sysdatabases
SET @lngCounter = @@ROWCOUNT
--Loop through databases to obtain members  of database roles and user-defined roles
WHILE @lngCounter > 0
BEGIN
--Get database name from temp table
SET @strDBName = (SELECT strDBName FROM #tDBNames WHERE lngID = @lngCounter)
--Obtain members of each database and user-defined role
SET @strSQL = 'INSERT INTO #tRolemember (strRoleName, strUserName, strUserID)
EXEC ' + @strDBName + '.dbo.sp_helprolemember'
EXEC sp_executesql @strSQL
--Update database name in temp table
UPDATE #tRolemember
SET strDBName = @strDBName
WHERE strDBName IS NULL
SET @lngCounter = @lngCounter - 1

END
--Place data into permanent table
INSERT INTO tRolemember
SELECT trm.* FROM #tRolemember trm
LEFT JOIN tRoleMember prm
ON trm.strUserName = prm.strUserName
AND trm.strDBName = prm.strDBName
AND trm.strRoleName = prm.strRoleName
AND trm.strServerName = prm.strServerName
WHERE prm.strServerName IS NULL
GO
--Test Stored Procedure
EXEC dbo.spRoleMembers
PRINT 'Display by User'
SELECT strUserName, strDBName, strRoleName, strServerName FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strUserName
PRINT 'Display by Role'
SELECT strRoleName, strDBName, strUserName,strServerName  FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strRoleName
PRINT 'Display by Database'
SELECT strDBName, strRoleName,strUserName, strServerName  FROM tRolemember
WHERE strUserName <> 'dbo'
ORDER BY strDBName