Scripting SQL Server permissions

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

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.

Expanding on the answer provided in https://stackoverflow.com/a/1987215/275388 which fails for database/schema wide rights and database user types you can use:

SELECT  CASE      WHEN dp.class_desc = 'OBJECT_OR_COLUMN' THEN        dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +         ' ON ' + '[' + obj_sch.name + ']' + '.' + '[' + o.name + ']' +        ' TO ' + '[' + dpr.name + ']'      WHEN dp.class_desc = 'DATABASE' THEN        dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +         ' TO ' + '[' + dpr.name + ']'      WHEN dp.class_desc = 'SCHEMA' THEN        dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +         ' ON SCHEMA :: ' + '[' + SCHEMA_NAME(dp.major_id) + ']' +        ' TO ' + '[' + dpr.name + ']'      WHEN dp.class_desc = 'TYPE' THEN        dp.state_desc + ' ' + dp.permission_name collate Latin1_General_CS_AS +         ' ON TYPE :: [' + s_types.name + '].[' + t.name + ']'            + ' TO [' + dpr.name + ']'      WHEN dp.class_desc = 'CERTIFICATE' THEN         dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +         ' TO ' + '[' + dpr.name + ']'       WHEN dp.class_desc = 'SYMMETRIC_KEYS' THEN         dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as +       ' TO ' + '[' + dpr.name + ']'       ELSE         'ERROR: Unhandled class_desc: ' + dp.class_desc  END AS GRANT_STMTFROM sys.database_permissions AS dp   JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id  LEFT JOIN sys.objects AS o ON dp.major_id=o.object_id  LEFT JOIN sys.schemas AS obj_sch ON o.schema_id = obj_sch.schema_id  LEFT JOIN sys.types AS t ON dp.major_id = t.user_type_id  LEFT JOIN sys.schemas AS s_types ON t.schema_id = s_types.schema_idWHERE dpr.name NOT IN ('public','guest') --  AND o.name IN ('My_Procedure')      -- Uncomment to filter to specific object(s)--  AND (o.name NOT IN ('My_Procedure') or o.name is null)  -- Uncomment to filter out specific object(s), but include rows with no o.name (VIEW DEFINITION etc.)--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs--  AND dpr.name LIKE '%user_name%'     -- Uncomment to filter to just matching usersORDER BY dpr.name, dp.class_desc, dp.permission_name