Fully Script out a MSSQL Database Role

A New Hope

SQL Server Management Studio is a phenomenal tool, especially when you want to script out complex tasks.  Do whatever you want in the GUI and click the handy little script button instead of OK and you’ve got a set of raw SQL that you can modify/reuse/etc.  This process works for a good majority of the operations you will find yourself doing as a DBA, but there’s one gaping hole that MS has yet to fill: scripting out Database Roles.  If you manage any level of database security, chances are you are using Database Roles.  If you were lucky enough to create them yourself, it’s not difficult to keep a script library to see what role allows for what permission(s).  However, the moment you inherit a database that contains custom Database Roles, you enter a world of hurt.  Traditionally, you can either painstakingly struggle with the GUI trying to associate various records and checkboxes with actual commands or you can throw in the towel and start from scratch.  Thankfully, there is a new way, as I’ve scripted out a process that will generate the role definition for you.

Tada!

Here’s the code; copy/paste and modify the role name, and wah-lah  you are now in the possession of the role definition.  If you find any bugs, please let me know with a comment.  Thanks!

/********************************************************************
 *                                                                  *
 * Author: John Eisbrener                                           *
 * Script Purpose: Script out Database Role Definition              *
 * Notes: Please report any bugs to http://www.dbaeyes.com/         *
 *                                                                  *
 * Update: 2014-03-03 - Adjusted output to accommodate Role         *
 *                      definitions that are longer than 8000 chars *
 * Update: 2013-09-03 - Added user output per Joe Spivey's comment  *
 *                    - Modified formatting for oddly named objects *
 *                    - Included support for Grants on DMVs         *
 ********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'

-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf

SELECT    @roleDesc = @roleDesc +
        CASE dp.state
            WHEN 'D' THEN 'DENY '
            WHEN 'G' THEN 'GRANT '
            WHEN 'R' THEN 'REVOKE '
            WHEN 'W' THEN 'GRANT '
        END + 
        dp.permission_name + ' ' +
        CASE dp.class
            WHEN 0 THEN ''
            WHEN 1 THEN --table or column subset on the table
                CASE WHEN dp.major_id < 0 THEN
                    + 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
                ELSE
                    + 'ON [' +
                    (SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
                        + -- optionally concatenate column names
                    CASE WHEN MAX(dp.minor_id) > 0 
                         THEN '] ([' + REPLACE(
                                        (SELECT name + '], [' 
                                         FROM sys.columns 
                                         WHERE object_id = dp.major_id 
                                            AND column_id IN (SELECT minor_id 
                                                              FROM sys.database_permissions 
                                                              WHERE major_id = dp.major_id
                                                                AND USER_NAME(grantee_principal_id) IN (@roleName)
                                                             )
                                         FOR XML PATH('')
                                        ) --replace final square bracket pair
                                    + '])', ', []', '')
                         ELSE ']'
                    END + ' '
                END
            WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
            WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
            WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
            WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
            WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
            WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
            WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
            WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
            WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
            WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
            WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
            WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
            WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
            WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
         END COLLATE SQL_Latin1_General_CP1_CI_AS
         + 'TO [' + @roleName + ']' + 
         CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM    sys.database_permissions dp
WHERE    USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class

SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf

-- Display users within Role.  Code stubbed by Joe Spivey
SELECT	@roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM	sys.database_principals users
		INNER JOIN sys.database_role_members link 
			ON link.member_principal_id = users.principal_id
		INNER JOIN sys.database_principals roles 
			ON roles.principal_id = link.role_principal_id
WHERE	roles.name = @roleName

-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000

WHILE LEN(@roleDesc) > 8000
BEGIN
    -- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
    SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))

    PRINT LEFT(@roleDesc, @printCur)
    SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END

PRINT @roleDesc + 'GO'

The Not-So-Devilish Details

If you’ve made it past the actual script above, I will assume you are interested in the whys and hows.  Effectively, all database role definitions are stored in the catalog view sys.database_permissions.  Each role has it’s own ID and based on some identifiers and lookup values, specific joins to tertiary tables are required.  Digging through the script will show you what tables relate to what keys.  Obviously it’s not entirely straight-forward otherwise this functionality would be more common knowledge.  As you dig through the script though, you will see some idiosyncrasies which I will explain.

The first had to do with table permissions.  You will see a GROUP BY clause which is a sneaky way to filter out duplicate records.  Permissions that relate explicitly to Table Columns are represented by the minor_id column in the database_permissions table.  Because of this you get many records for one table permission operation, and since the same command doesn’t need to be executed multiple times, it’s best to eliminate the duplicate records.  This is also the reason for the MAX aggregate function around the minor_id column.

The second item had to do with how elaborate the GRANT clause can be in general.  You can do a LOT with a GRANT covering a wide-range of objects as is evident by the script above.  To be honest, I’ve ever only had to grant permissions to three of these types (e.g. tables, database-level, and database roles), but thankfully with a little digging it’s relatively easy to discern the syntax for those I’ve not directly worked with.

The final quirk had to do with database roles and users.  Both are stored in the database_principals table, and the syntax is different for each.  Thankfully ROLE and USER are both 4-letter words, hence the RIGHT function.

In closing, I hope this comes in handy for others as I’ve already experienced a good deal of use out of it myself.  Maybe a Connect Submission is in order to have MS add this as the call for the Database Role script -> CREATE AS option?  Time will tell.

Subscribe to the post in case more updates find there way here.  Thanks!

John

UPDATES

UPDATE (2013-09-03): Thanks to a comment left by Joe Spivey, I’ve updated the code to also output the users currently in the role.  I’ve also taken the opportunity to update it to qualify table and column names within square brakets.  The final adjustment has been a slight alteration to accommodate explicit GRANTS on DMVs.  DMVs have negative object IDs and were nulling out the entirety of the script.  This has since been resolved, and hopefully the script is in it’s final state.

UPDATE (2014-03-03): If you find that you have a role which contains a large number of explicit grants/deny’s you’ll quickly find out that this script will cut off the definition at 8000 characters.  This is a limitation of the PRINT command and not the actual routine itself.  To correct this flaw, I’ve updated the final statement to iterate through the final output utilizing CRLFs as the cutoff point for the output so that it looks clean.

NOTE (2014-03-03): If you are applying a role definition to a recently refreshed database (e.g. you copied a PROD backup down to your QA environment), you may run into a situation where the sp_AddRoleMember system sp adds users to the DB that weren’t previously there.  In this case, even though they are added to the db they are NOT granted the CONNECT permission, and any connection attempt made to the recently refreshed db by said user or group will generate a user login error.  To rectify this issue, you need to execute the following per user/group:

USE [DatabaseName]
GO
GRANT CONNECT TO [Login/GroupName]
GO

References:
http://msdn.microsoft.com/en-us/library/ms189121.aspx