How to script SQL server database role?

Question

I need to make a script to copy one particular database role from one SQL server to another.

Is there an easy way to generate a script that creates the role and all the role permissions?

Answer 1

You can get what you need with a script like this:

declare @RoleName varchar(50) = 'RoleName'  declare @Script varchar(max) = 'CREATE ROLE ' + @RoleName + char(13) select @script = @script + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS  from sys.database_permissions prm     join sys.database_principals rol on         prm.grantee_principal_id = rol.principal_id where rol.name = @RoleName  print @script 

Answer 2

I've made a pretty comprehensive script that not only scripts out all the permissions, but also all membership, and to put frosting on the cake formats the output for easy copy/pasting into a new query window. I've posted the script to my blog and update it from time-to-time, but below is the current version which should cover most bases:

/********************************************************************  *                                                                  *  * Author: John Eisbrener                                           *  * Script Purpose: Script out Database Role Definition              *  * Notes: Please report any bugs to http://www.dbaeyes.com/         *  *                                                                  *  ********************************************************************/ 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' 

Of note, you may run into a situation where the sp_AddRoleMember system sp adds user(s) to the DB that wasn’t previously there. In this case, even though the user(s) is added, they are NOT granted the CONNECT permission, and any connection attempt made by said user or group will generate a user login error. To rectify this issue, you need to execute the following per new user/group within the db:

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