Scripting SQL Server permissions

user_10082 0
Viewed 131130

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).

sql-server
9 Answers

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).

user_100820

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

user_275350 
declare @DBRoleName varchar(40) = 'yourUserName'
SELECT 'GRANT ' + dbprm.permission_name + ' ON ' + OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) + ' TO ' + dbrol.name + char(13) COLLATE Latin1_General_CI_AS
from sys.database_permissions dbprm
join sys.database_principals dbrol on
dbprm.grantee_principal_id = dbrol.principal_id
where dbrol.name = @DBRoleName

http://www.sqlserver-dba.com/2014/10/how-to-script-database-role-permissions-and-securables.html

I found this to be an excellent solution for generating a script to replicate access between environments

user_16384780
Related