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 'GRANT ' + dp.permission_name collate latin1_general_cs_as
    + ' ON ' + s.name + '.' + o.name + ' TO ' + dpr.name 
    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 permission_name='EXECUTE'

This will spit out a bunch of GRANT commands 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

Yes, you can use a script like this to generate another script

SET NOCOUNT ON;
DECLARE @NewRole varchar(100), @SourceRole varchar(100);

-- Change as needed
SELECT @SourceRole = 'Giver', @NewRole = 'Taker';

SELECT
    state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) ' TO ' + @NewRole
FROM
    sys.database_permissions
WHERE
    grantee_principal_id = DATABASE_PRINCIPAL_ID(@SourceRole) AND
    -- 0 = DB,  1 = object/column, 3 = schema. 1 is normally enough
    class <= 3

This is taken from my answer here