对一组存储过程授予Exec

Question

我在数据库中有大约500个以mysp_为前缀的过程。

目前我有两个角色，Role1和Role2，执行过程的权限分布在它们之间。

我需要删除Role2（这不是问题），然后在Role1的每个过程中删除Grant Exec。

有人能建议怎么做吗？我可以很容易地在单个过程中Grant，但不能在选择的过程中（例如，前缀为mysp_的过程）。

Answer 1

您可以使用动态SQL和游标来迭代过程：

DECLARE @ProcName VARCHAR(100)
DECLARE @SQL VARCHAR(100)
DECLARE DynamicSQL CURSOR FOR
SELECT Name FROM sysobjects WHERE xtype = 'P' and Name like 'mysp_%'
OPEN DynamicSQL
FETCH NEXT FROM DynamicSQL INTO @ProcName
WHILE @@Fetch_Status = 0
BEGIN
  SET @SQL = 'GRANT EXEC ON ' + @ProcName + ' TO Role1'
  PRINT @SQL
  --EXEC(@SQL)
FETCH NEXT FROM DynamicSQL INTO @ProcName
END
CLOSE DynamicSQL
DEALLOCATE DynamicSQL

一旦您对输出感到满意，只需取消对EXEC部分的注释即可！

Answer 2

这个怎么样？

SELECT
    'GRANT EXECUTE ON ' + OBJECT_SCHEMA_NAME(object_id) + '.' + name + ' TO Role1'
FROM 
    sys.procedures
WHERE
    Name LIKE 'mysp%'

这将生成一个语句列表作为输出，您可以将其从输出网格复制到一个新的查询窗口中，然后执行-现在您的所有过程都被授予Role1！