授予用户对数据库中所有存储过程的执行权限? [英] Grant execute permission for a user on all stored procedures in database?

问题描述

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

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] 

如果只是几个过程，那么，我有大约 100 个过程，那么我将特定用户的执行访问权限授予所有这些过程的最简单方法是什么?

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?

提前致谢.

推荐答案

创建一个角色 将此角色添加给用户，然后您就可以将所有例程的执行权限授予该角色.

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>

编辑
这在 SQL Server 2005 中有效，我不确定此功能的向后兼容性，我确定 2005 之后的任何内容都可以.

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.

这篇关于授予用户对数据库中所有存储过程的执行权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！