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.
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
- 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
Permission script for all object