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

我从旧数据库生成脚本，创建一个新的数据库，并从旧数据库导入所有数据。但是，到目前为止还不错，没有用户拥有存储过程的执行权限。我知道我可以利用

GRANT EXECUTE ON [storedProcName] TO [userName]

但是，如果只有几个过程，那么我有大约100个过程，那么对于我来说，向所有过程授予特定用户的执行访问权限最简单的方法是什么？

402528 次浏览
小开
最佳答案

创建一个角色将此角色添加到用户，然后可以一次性将所有例程的执行授予此角色。

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

剪辑
这在 SQL Server 2005中可以工作，我不确定这个特性的向下兼容，我确定在2005之后的任何东西都可以。

小开

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

USE <database_name>
select 'GRANT EXECUTE ON ['+name+'] TO [userName]  '
from sys.objects
where type ='P'
and is_ms_shipped = 0
小开 
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
小开

这是一种解决方案，意味着当您向模式添加新的存储过程时，用户可以执行这些过程，而不必调用新存储过程上的权限执行:

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

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

小开

在不使问题过于复杂的情况下，在选定的数据库上授予 EXECUTE:

USE [DB]
GRANT EXEC TO [User_Name];

提交答案