为什么没有内置的“存储过程执行器”？数据库角色？
我一直想知道为什么数据库角色列表（db_datareader、db_datawriter、db_ddladmin等）从未包含db_storedprocedureexecutor代码>.其他的都是有道理的，但似乎能够向特定用户授予执行所有存储过程的能力（而不授予他们db_owner，这是完成同一件事的唯一其他方法）是一个方便的东西。
例如，假设我想授予所有开发人员运行存储过程的权限，同时不让他们执行任何 DDL - 无需在每个存储过程上显式授予 EXECUTE（然后记住在部署其他 SP 时添加新的存储过程） ，没有办法做到这一点（我知道 SP 可以包含 DDL，因此它们可能仍然可以通过这种方式间接访问 DDL）。
如果我有一个应用程序服务帐户和许多与我的应用程序一起使用的存储过程，我必须明确向每个 SP 授予权限（因为我不想授予我的应用程序服务帐户 DBO），而我可以使用角色允许他们更新/删除他们想要的任何内容。
虽然它最初看起来很明显，但我现在不确定为什么数据库服务器中缺少这个角色 - 任何人都可以解释为什么这是一个糟糕的想法（正如我假设的那样，或者它已经存在）？
编辑：
似乎我不是唯一有这种期望的人 - 并且它可以通过一些 T-SQL 解决（看来你可以授予一揽子 EXECUTE 权限，我不知道你可以这样做），这让我想知道为什么它不标准！
I've always wondered why the list of database roles (db_datareader, db_datawriter, db_ddladmin, etc) never included a db_storedprocedureexecutor. The others make sense, but it seems like being able to grant the ability to execute all stored procs to a particular user (without granting them db_owner, which is the only other way to accomplish the same thing) would be a handy thing.
For example, say I want to give all of my developers the rights to run stored procedures, while not let them execute any DDL - without explicitly granting EXECUTE on every stored procedure (and then remembering to add the new ones when additional SPs are deployed), there's not way to do this (I know that SPs can contain DDL, so they may still be indirectly allowed access to DDL this way).
If I have an application service account and a number of stored procedures that go along with my application, I have to grant rights explicitly to every SP (since I don't want to grant my application service account DBO), whereas I can use a role to allow them to update/delete anything they want.
While it initially seemed obvious, I'm now not sure why this role is missing from the database server - can anybody offer an explanation of why this is a terrible idea (as I assume it is, or it would already exist)?
EDIT:
Seems I'm not the only one with this expectation - and it's worked around with a handful of T-SQL (it seems you can grant a blanket EXECUTE right, which I wasn't aware you could do), which just leaves me wondering why it's not standard!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用模式，那么您只需
GRANT EXECUTE ON SCHEMA::storedprocschema例如
CREATE PROCstoredprocschema.DoStuff ...至于为什么，不知道...
If you use schemas, then you only have to
GRANT EXECUTE ON SCHEMA::storedprocschemaeg
CREATE PROC storedprocschema.DoStuff ...As to why, no idea...
因为如果您可以执行所有存储过程，则可以执行
sp_addrolemember，并且您可以执行database_owner可以执行的所有操作。Because if you can execute all stored procedures, you can execute
sp_addrolemember, and you can do everything that adatabase_ownercan.