Auditing Your SQL Server Environment, Part II (Reviewing Role Memberships)

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 member
   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                                                               
  
************************************************************/
SET NOCOUNT ON

–Variables
DECLARE @lngCounter INTEGER
DECLARE @strDBName VARCHAR(50)
DECLARE @strSQL NVARCHAR(4000)

–Temp table to hold database and user-define 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.

Copyright 2002 by Randy Dyess, All Rights Reserved. Reprinted with permission.

 

]]>

Leave a comment

Your email address will not be published.