SQLServerCentral Article

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

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

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating