Auditing Your SQL Server Environment Part II
(Reviewing your Role Membership)

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

Summary
Roles should be in foremost in your mind when planning the security of your SQL Server environments. Auditing inherited SQL Server installations is a relatively easy thing to accomplish and all DBAs should audit their environment and create documentation if they have not already done so. Once you can document the logins assigned to each of your fixed and user-defined roles you can start to remove any duplication of permissions which can reduce the time needed to troubleshoot future permission errors. The next installment of this series will show you how to create a master list of role and login permissions to help you accomplish the task of removing duplicate permissions as well as reassigning permissions to roles instead of individual logins.

Knowledge Based Articles
Q303879 INF: Maximum Number of Database Users and Roles That You Can Create
Q201021 BUG: DOC: DB_OWNER Roles Cannot Administer All Jobs /span>
Q203824 BUG: Incomplete Display of Some System Roles
Q229564 PRB: SQL Application Role Errors with OLE DB Resource Pooling
Q231403 BUG: BOL Incorrectly States That a Member of db_owner Role Can Issue SETUSER Command
Q243053 HOWTO: Create an Application Role on Microsoft SQL Server 7.0
Q271258 BUG: Original db_datareader Role is Lost if dbuser is Assigned a New db_datawriter Role
Q302621 BUG: Cannot Perform BULK INSERT with Bulkadmin Privileges
Q207718 BUG: User's Role Not Updated Instantly in Multiple Sessions
Q241636 BUG: BOL Incorrectly Documents Permissions to System Stored Procedures
Q295121 BUG: DOC Error: Documentation Incorrectly States that db_owner role has Restore Database Permissions
Q318733 FIX: SQL Server May Become Unresponsive When a Large Number of Databases and Roles are Used



Copyright 2002 by Randy Dyess, All rights Reserved