|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
/******************************************************************** * * * 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' |
Thanks for the script to John Eisbrener – Check for new version here: https://dbaeyes.wordpress.com/2013/04/19/fully-script-out-a-mssql-database-role/