如何授予数据库角色对架构的执行权限？我做错了什么？
我使用的是 SQL Server 2008 Express 版本。
我创建了登录名、用户、角色和架构。 我已将用户映射到登录名，并将角色分配给用户。
该架构包含许多表和存储过程。
我希望角色拥有整个架构的执行权限。
我尝试通过管理工作室并通过在查询窗口中输入命令来授予执行权限。
GRANT EXEC ON SCHEMA::schema_name TO role_name
但是，当我使用 SQL Management Studio 连接到数据库（作为我创建的登录名）时，首先我看不到存储过程，但更重要的是，在尝试运行它们时，我收到权限被拒绝错误。
所讨论的存储过程除了从同一模式内的表中选择数据之外什么也不做。
我尝试过使用和不使用以下行创建存储过程：
WITH EXECUTE AS OWNER
这没有任何区别。
我怀疑我在创建模式时犯了错误，或者某处存在所有权问题，但我真的很难让某些东西正常工作。
我成功执行存储过程的唯一方法是向角色授予控制权限并执行，但我不认为这是正确、安全的继续方法。
任何建议/意见将不胜感激。
谢谢。
I am using SQL Server 2008 Express edition.
I have created a Login , User, Role and Schema.
I have mapped the user to the login, and assigned the role to the user.
The schema contains a number of tables and stored procedures.
I would like the Role to have execute permissions on the entire schema.
I have tried granting execute permission through management studio and through entering the command in a query window.
GRANT EXEC ON SCHEMA::schema_name TO role_name
But When I connect to the database using SQL management studio (as the login I have created) firstly I cannot see the stored procedures, but more importantly I get a permission denied error when attempting to run them.
The stored procedure in question does nothing except select data from a table within the same schma.
I have tried creating the stored procedure with and without the line:
WITH EXECUTE AS OWNER
This doesn't make any difference.
I suspect that I have made an error when creating my schema, or there is an ownership issue somewhere, but I am really struggling to get something working.
The only way I have successfully managed to execute the stored procedures is by granting control permissions to the role as well as execute, but I don't believe this is the correct, secure way to proceed.
Any suggestions/comments would be really appreciated.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在你的案例中我可以看到几个问题。
首先，您需要授予查看定义才能查看 Management studio 中的对象。
如果您希望角色拥有所有权限，我建议您这样做，
还要确保您的用户定义架构的所有者是“dbo”。
There are couple of issues that I can see in your case.
First of all you would need View Definition granted for you to be able to see the objects in the Management studio.
I would recommend this if you want the role to have all permissions,
Also make sure the owner of your user-defined schema is "dbo".