编写 SQL Server 权限脚本

我想将我对存储过程和其他内容设置的所有权限从开发数据库复制到生产数据库。通过 SSMS GUI 工具手动完成这一切非常麻烦，更不用说容易出错。

因此，我正在寻找一种方法，可以直接转储在一个数据库中设置的权限，并将这些相同的权限应用于单独的数据库（大概具有相同的架构）。

数据库的内置目录视图提供了执行此操作的信息。尝试这个查询：

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

这将为数据库中的每个权限发出一堆命令（GRANT/DENY）。由此，您可以将它们复制并粘贴到另一个查询窗口中并执行，以生成与原始查询窗口相同的权限。例如：

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]

请注意注释掉的底线，即对permission_name 进行过滤。取消注释该行将导致查询仅吐出 EXECUTE 权限（即存储过程的权限）。