Generate Permission script for an object or all object

In the DBA world it’s necessary to pickup permissions for an object for all objects when he\she deploys the code. During deployment if the code has DROP and CREATE then it’s obvious that what ever permission you have assigned to that object will get lost so as a first step you need to script out permissions for the objects. You can get similar information using sp_helprotect procedure but you need to manually write the grant script from the output and you can’t use it for multiple or all objects. This is where I felt to write a script which can fetch all the permission related information for an object or for all the objects using sys.database_permissions dmv.

Script

DECLARE @tmp TABLE(permission VARCHAR(4000))
 INSERT INTO @tmp
 select a.state_desc + ' '
+ a.permission_name  + ' ON '
+ QUOTENAME(SCHEMA_NAME(O.uid)) +'.'
+ QUOTENAME(O.name) + ' TO '
+ QUOTENAME (U.name)
 from sys.database_permissions a
 join sys.sysusers U on grantee_principal_id = uid
 join sys.sysobjects O on major_id = id
 --Optional if you are looking for a particular object then uncomment below line and update the object name
--WHERE O.name IN ('objectname')

--Optional, if the object schema is different then uncomment below line and change the schema name
--AND schema_name(O.uid) IN ('dbo') 

--Optional, if you need to script for a particular user then uncomment below line and upadte the user name. 
--AND U.name='test'
SELECT * FROM @tmp

Updated: Version2

  • Earlier script will always give you GRANT script only now this includes revoke script.. In other words what ever permission its there in system, this will fetch
  • No need for additional temp table
  • You can script permissions for all object, single object or for a particular schema or for a particular user.

Permission script for individual object

 

generate_permission_script_1

Permission script for all object

generate_permission_script_2

Leave a Reply

Your email address will not be published. Required fields are marked *

*