在数据库中为用户授予对所有存储过程的执行权限？

Question

我从旧数据库生成了脚本，创建了一个新数据库并将所有数据从旧数据库导入。到目前为止一切都很好，但是没有用户有存储过程的执行权限。我知道可以使用

GRANT EXECUTE ON [storedProcName] TO [userName] 

如果只有一些过程，那么问题不大，但我有大约100个过程，那么最简单的方式是什么，可以让特定用户对它们全部拥有执行权限？

- Nick

Answer 1

创建一个角色，将该角色分配给用户，然后您可以一次性授予此角色对所有程序的执行权限。

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

编辑
这在SQL Server 2005中有效，我不确定此功能的向后兼容性，但我确信任何比2005更晚的版本都应该没问题。

- Sanjeevakumar Hiremath

---

我刚在 SQL Server 2008 Standard (Amazon RDS) 上尝试了这个，结果非常顺利。 - datagod

请问您能否提供一个例子？比如说，我需要为用户SPExecuter授予所有存储过程的EXECUTE权限。 - Uri Abramson

所需的唯一其他语句是将用户添加到角色的语句，如下所示：ALTER ROLE [abc] ADD MEMBER [user_name]。 - dhochee

您实际上不需要创建一个角色，您可以直接将其应用于用户，例如：GRANT EXECUTE TO userName。我认为这对于问题的提出者来说已经足够了。 - Chris Peacock

问题是如何给一个用户权限，而不是如何给角色权限，我认为正确的答案是Bartosz X的。GRANT EXEC TO [User_Name]; - lisandro

Answer 2

不过多复杂化问题，授予所选数据库的EXECUTE权限：

USE [DB]
GRANT EXEC TO [User_Name];

- Bartosz X

---

对我来说有效，而且可能适用于所有未来的存储过程（我们会找出来），而不是为每个存储过程命名的脚本。 - Ken Forslund

Answer 3

这是一个解决方案，意味着当您向模式中添加新的存储过程时，用户可以执行它们，而无需调用授予对新存储过程的执行权限：

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'asp_net')
DROP USER asp_net
GO

IF  EXISTS (SELECT * FROM sys.database_principals 
WHERE name = N'db_execproc' AND type = 'R')
DROP ROLE [db_execproc]
GO

--Create a database role....
CREATE ROLE [db_execproc] AUTHORIZATION [dbo]
GO

--...with EXECUTE permission at the schema level...
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

--http://www.patrickkeisler.com/2012/10/grant-execute-permission-on-all-stored.html
--Any stored procedures that are created in the dbo schema can be 
--executed by users who are members of the db_execproc database role

--...add a user e.g. for the NETWORK SERVICE login that asp.net uses
CREATE USER asp_net 
FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] 
WITH DEFAULT_SCHEMA=[dbo]
GO

--...and add them to the roles you need
EXEC sp_addrolemember N'db_execproc', 'asp_net';
EXEC sp_addrolemember N'db_datareader', 'asp_net';
EXEC sp_addrolemember N'db_datawriter', 'asp_net';
GO

参考资料：授予所有存储过程执行权限

- Colin

Answer 4

使用以下代码，更改适当的数据库名称和用户名，然后获取该输出并在SSMS中执行。对于SQL 2005以上版本。

USE <database_name> 
select 'GRANT EXECUTE ON ['+name+'] TO [userName]  '  
from sys.objects  
where type ='P' 
and is_ms_shipped = 0  

- Hemanshu Trivedi

---

你需要将“PC”类型也包含在内，以便包括CLR存储过程。 - Oleh Nechytailo

Answer 5

USE [DATABASE]

DECLARE @USERNAME VARCHAR(500)

DECLARE @STRSQL NVARCHAR(MAX)

SET @USERNAME='[USERNAME] '
SET @STRSQL=''

select @STRSQL+=CHAR(13)+'GRANT EXECUTE ON ['+ s.name+'].['+obj.name+'] TO'+@USERNAME+';'
from
    sys.all_objects as obj
inner join
    sys.schemas s ON obj.schema_id = s.schema_id
where obj.type in ('P','V','FK')
AND s.NAME NOT IN ('SYS','INFORMATION_SCHEMA')


EXEC SP_EXECUTESQL @STRSQL

- GCH