使用“执行 sp_Executesql”时的权限
我有一个数据库，其中所有访问都由存储过程控制。 DBA 希望避免向用户提供对基础表的直接读/写访问权限，这一点我可以理解。因此，所有数据的更新和选择都是通过存储过程完成的。基本上，他创建了一个角色，该角色对数据库中的所有存储过程具有 EXECUTE 权限，并向用户授予该角色。
问题是存储过程之一动态构建 SQl 查询并通过“执行 sp_Executesql”执行它。无需详细说明，查询是动态构建的，因为它会根据许多用户输入参数而发生显着变化。有问题的存储过程只是一个 SELECT sql 语句，但是我发现仅授予存储过程 EXECUTE 权限是不够的。使用“执行 sp_Executesql”的存储过程中引用的基础表需要被授予“datareader”访问权限，否则存储过程将失败。
关于如何纠正这个问题有什么想法吗？我确实想将对表的访问限制为仅存储过程，但我需要找到一种方法来解决使用“执行 sp_Executesq”的存储过程。谢谢。
I have a database where all access is controlled by stored procedures. The DBA would like to avoid giving users direct read/write access to the underlying tables, which I can understand. Hence all updating and selecting of data is done via stored procedures. Basically he has created one role that has EXECUTE permissions to all the stored procedures in the database and given users that role.
The problem is that one of the stored procedures dynamically builds a SQl Query and executes it via "Execute sp_Executesql". Without going into great detail the query is built dynamically because it changes significantly depending on many user input parameters. The stored procedure in question is only a SELECT sql statement however I am finding that just giving the stored procedure EXECUTE permission is not enough. The underlying tables referenced within the stored procedure that make use of "Execute sp_Executesql" need to have been given "datareader" access or else the stored procedure fails.
Any thoughts on how to correct this? I really wanted to restrict access to the tables to only stored procedures, but I need to find a way to work around the stored procedures that make use of "Execute sp_Executesq"l. Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在包装过程中，您可以使用 EXECUTE AS OWNER 或 EXECUTE AS SomeuserWithNoLogin
这将更改包含 sp_executesql 的存储过程期间的登录上下文。
EXECUTE AS需要一个条目sys.database_principals如下所示：
有关详细信息，请参阅 MSDN 上的 EXECUTE AS 子句 和
创建过程In the wrapper proc you can use
EXECUTE AS OWNERorEXECUTE AS SomeuserWithNoLoginThis will change the login context for the duration of the stored proc which includes sp_executesql.
EXECUTE AS <user>requires an entry issys.database_principalsLike this:
For more info, see EXECUTE AS Clause on MSDN and
CREATE PROCEDURE真正的问题是 sp_Executesql 位于 master 数据库中，而不一定是您工作的数据库中。您的 DBA 必须向调用过程授予执行 sp_Executesql 权限。任何有权调用该过程的人都可以运行 sp_Executesql。
The real problem is that sp_Executesql is in the master database, not necessarily the database your working in. Your DBA has to give execute sp_Executesql permission to the calling procedure. Than anyone who has permission to call that procedure will be able to run the sp_Executesql.