Script Database Role –Very god

http://dbaeyes.wordpress.com/2013/04/19/fully-script-out-a-mssql-database-role/

 

DECLARE

@roleName VARCHAR(255

)

 

SET

@roleName =

‘DB_FLIP_TESTING’

 

 

 

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.stateWHEN‘D’THEN‘DENY ‘WHEN‘G’THEN‘GRANT ‘WHEN‘R’THEN‘REVOKE ‘WHEN‘W’THEN‘GRANT ‘END+

dp

.permission_name +‘ ‘+CASE dp.classWHEN 0 THENWHEN 1 THEN–table or column subset on the table+‘ON ‘+(SELECTSCHEMA_NAME(schema_id)+‘.’+nameFROMsys.objectsWHEREobject_id= dp.major_id)+— optionally concatenate column namesCASEWHENMAX(dp.minor_id)> 0 THEN‘ (‘+REPLACE((SELECTname+‘, ‘FROMsys.columnsWHEREobject_id= dp.major_id AND column_id IN(SELECT minor_id FROMsys.database_permissionsWHERE major_id = dp.major_idANDUSER_NAME(grantee_principal_id)IN(@roleName))FORXMLPATH())–remove final comma+‘)’,‘, )’,‘)’)ELSEEND+‘ ‘WHEN 3 THEN‘ON SCHEMA::[‘+SCHEMA_NAME(dp.major_id)+‘] ‘WHEN 4 THEN‘ON ‘+(SELECTRIGHT(type_desc, 4)+‘::[‘+nameFROMsys.database_principalsWHERE principal_id = dp.major_id)+‘] ‘WHEN 5 THEN‘ON ASSEMBLY::[‘+(SELECTnameFROMsys.assembliesWHERE assembly_id = dp.major_id)+‘] ‘WHEN 6 THEN‘ON TYPE::[‘+(SELECTnameFROMsys.typesWHERE user_type_id = dp.major_id)+‘] ‘WHEN 10 THEN‘ON XML SCHEMA COLLECTION::[‘+(SELECTSCHEMA_NAME(schema_id)+‘.’+nameFROMsys.xml_schema_collectionsWHERE xml_collection_id = dp.major_id)+‘] ‘WHEN 15 THEN‘ON MESSAGE TYPE::[‘+(SELECTnameFROMsys.service_message_typesWHERE message_type_id = dp.major_id)+‘] ‘WHEN 16 THEN‘ON CONTRACT::[‘+(SELECTnameFROMsys.service_contractsWHERE service_contract_id = dp.major_id)+‘] ‘WHEN 17 THEN‘ON SERVICE::[‘+(SELECTnameFROMsys.servicesWHERE service_id = dp.major_id)+‘] ‘WHEN 18 THEN‘ON REMOTE SERVICE BINDING::[‘+(SELECTnameFROMsys.remote_service_bindingsWHERE remote_service_binding_id = dp.major_id)+‘] ‘WHEN 19 THEN‘ON ROUTE::[‘+(SELECTnameFROMsys.routesWHERE route_id = dp.major_id)+‘] ‘WHEN 23 THEN‘ON FULLTEXT CATALOG::[‘+(SELECTnameFROMsys.fulltext_catalogsWHERE fulltext_catalog_id = dp.major_id)+‘] ‘WHEN 24 THEN‘ON SYMMETRIC KEY::[‘+(SELECTnameFROMsys.symmetric_keysWHERE symmetric_key_id = dp.major_id)+‘] ‘WHEN 25 THEN‘ON CERTIFICATE::[‘+(SELECTnameFROMsys.certificatesWHERE certificate_id = dp.major_id)+‘] ‘WHEN 26 THEN‘ON ASYMMETRIC KEY::[‘+(SELECTnameFROMsys.asymmetric_keysWHERE asymmetric_key_id = dp.major_id)+‘] ‘ENDCOLLATE SQL_Latin1_General_CP1_CI_AS+‘TO [‘+ @roleName +‘]’+CASE dp.state WHEN‘W’THEN‘ WITH GRANT OPTION’ELSEEND+ @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

PRINT @roleDesc

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s