如何在特定模式中授予存储过程执行权限？

Question

在 SQL Server 2012 中，是否有一种方法可以授予某个模式中所有存储过程的执行权限？需要额外说明的是，这些存储过程只执行 SELECT 操作。

- Andi

Answer 1

试着这样做。它会创建一个新的角色并授予一个模式的执行权限。

CREATE ROLE db_executor
GRANT EXECUTE ON SCHEMA::schema_name TO db_executor
exec sp_addrolemember 'db_executor', 'Username'

将schema_name替换为您的模式，将'Username'替换为您的用户名。

- Szymon

---

是否可以将SCHEMA权限授予已经存在的SQL SERVER角色，例如db_datareader？ - Fatih Yarbaşı

@FatihYarbaşı 这应该是可能的，但我不确定内置角色是否支持。我建议不要扩展内置角色，最好创建自己的自定义角色。 - Szymon

Answer 2

如果要为一个模式中的所有存储过程授权执行权限，则@szymon提供的查询已足够。

以下查询将授予所选用户对该存储过程的执行权限。前提是该用户已存在。

GRANT EXECUTE ON OBJECT::[schema].[procedurename] TO [user] AS [schema]; GO

- Abhishek Chakraborty

Answer 3

我建议您按照Szymon的建议去做，因为这是更优雅的解决方案。但是，如果您想避免创建角色，可以执行以下查询，将结果复制到单独的查询编辑器中，并一次性执行所有内容。

这将为特定模式中所有存储过程生成GRANT EXECUTE语句。

DECLARE @SchemaName varchar(20)
DECLARE @UserName varchar(20)

SET @SchemaName = 'dbo'
SET @UserName = 'user_name'

select 'GRANT EXECUTE ON OBJECT::' + @SchemaName + '.' + P.name  + ' to ' + @UserName
from sys.procedures P
inner join sys.schemas S on P.schema_id = S.schema_id
where S.name = @SchemaName

- Kenneth Hampton

---

但是如果新的存储过程将被创建呢？我认为您也需要授予新创建的存储过程执行权限，对吗？ - Fatih Yarbaşı