SQL 授予用户对数据库中所有存储过程的执行权限？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5428406/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grant execute permission for a user on all stored procedures in database?
提问by Nick
I generated script from old database, created a new database and imported all data from old database. So far so good, however, no user has execute rights for stored procedures. I know I can use
我从旧数据库生成脚本，创建一个新数据库并从旧数据库导入所有数据。到目前为止一切顺利，但是，没有用户拥有存储过程的执行权限。我知道我可以使用
GRANT EXECUTE ON [storedProcName] TO [userName]
If it was just a few procedures, however, I have about 100 so what's the easiest way for me to grant execute access for a specific user to all of them?
但是，如果它只是几个过程，那么我有大约 100 个过程，那么我将特定用户的执行访问权限授予所有这些过程的最简单方法是什么？
Thanks in advance.
提前致谢。
回答by Sanjeevakumar Hiremath
Create a role add this role to users, and then you can grant execute to all the routines in one shot to this role.
创建一个角色将此角色添加到用户，然后您可以将执行一次所有例程授予该角色。
CREATE ROLE <abc>
GRANT EXECUTE TO <abc>
EDIT
This works in SQL Server 2005, I'm not sure about backward compatibility of this feature, I'm sure anything later than 2005 should be fine.
编辑
这适用于 SQL Server 2005，我不确定此功能的向后兼容性，我确定 2005 年以后的任何内容都可以。
回答by Bartosz X
Without over-complicating the problem, to grant the EXECUTE on chosen database:
在不使问题过于复杂的情况下，在所选数据库上授予 EXECUTE：
USE [DB]
GRANT EXEC TO [User_Name];
回答by Colin
This is a solution that means that as you add new stored procedures to the schema, users can execute them without having to call grant execute on the new stored procedure:
这是一个解决方案，这意味着当您向架构添加新的存储过程时，用户可以执行它们而无需在新存储过程上调用 grant execute：
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
Reference: Grant Execute Permission on All Stored Procedures
回答by Hemanshu Trivedi
use below code , change proper database name and user name and then take that output and execute in SSMS. FOR SQL 2005 ABOVE
使用下面的代码，更改正确的数据库名称和用户名，然后获取该输出并在 SSMS 中执行。上面的 SQL 2005
USE <database_name>
select 'GRANT EXECUTE ON ['+name+'] TO [userName] '
from sys.objects
where type ='P'
and is_ms_shipped = 0
回答by GCH
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
