Scripting SQL Server permissions

Question

I want to copy all the permission I've set on stored procedures and other stuff from my development database to my production database. It's incredibly cumbersome, not to mention error prone, to do this all by hand through the SSMS GUI tool.

So I'm looking for a way I can directly dump the permissions set in one database, and apply those same permissions to a separate database (presumably having the same schema).

Answer 1

The database's built-in catalog views provide the information to do this. Try this query:

SELECT   (     dp.state_desc + ' ' +     dp.permission_name collate latin1_general_cs_as +      ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +     ' TO ' + '[' + dpr.name + ']'   ) AS GRANT_STMT FROM sys.database_permissions AS dp   INNER JOIN sys.objects AS o ON dp.major_id=o.object_id   INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id   INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id WHERE dpr.name NOT IN ('public','guest') --  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s) --  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs 

This will spit out a bunch of commands (GRANT/DENY) for each of the permissions in the database. From this, you can copy-and-paste them into another query window and execute, to generate the same permissions that were in place on the original. For example:

GRANT EXECUTE ON [Exposed].[EmployeePunchoutReservationRetrieve] TO [CustomerAgentRole] GRANT EXECUTE ON [Exposed].[EmployeePunchoutReservationStore] TO [CustomerAgentRole] GRANT EXECUTE ON [Exposed].[EmployeePunchoutSendOrderLogStore] TO [CustomerAgentRole] GRANT EXECUTE ON [Exposed].[EmployeeReportSubscriptions] TO [CustomerAgentRole] 

Note the bottom line, commented out, that's filtering on permission_name. Un-commenting that line will cause the query to only spit out the EXECUTE permissions (i.e., those for stored procedures).

Answer 2

You can get SQL Server Management Studio to do it for you:

• Right click the database you want to export permissions for
• Select 'Tasks' then 'Generate Scripts...'
• Confirm the database you're scripting
• Set the following scripting options:
  • Script Create: FALSE
  • Script Object-Level Permissions: TRUE
• Select the object types whose permission you want to script
• Select the objects whose permission you want to script
• Select where you want the script produced

This will produce a script to set permissions for all selected objects but suppresses the object scripts themselves.

This is based on the dialog for MS SQL 2008 with all other scripting options unchanged from install defaults.