使用动态 SQL 对存储过程的 SQL Server 权限
我有一个具有应用程序角色的数据库。角色成员都属于 Active Directory 中的一个组。我没有授予角色从表中进行选择的权限，而是授予角色对其需要调用的所有存储过程的执行权限。
除了我的一个存储过程正在构建一些动态 SQL 并调用 sp_executesql 之外，这工作得很好。
动态 sql 看起来有点像这样：
SET @SQL = N'
SELECT *
FROM dbo.uvView1
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'
EXEC sp_executesql @SQL
此角色的用户无法调用存储过程。它给出了以下错误，这是我所期望的：
对象“uvView1”、数据库“Foobar”、模式“dbo”的 SELECT 权限被拒绝。
有没有办法让我的用户成功执行此过程，而无需授予动态 SQL 中所有视图的角色权限？
I have a database which has an application role. The role members all belong to a group in Active Directory. Instead of giving the role permissions to select from the tables I have given the role execute permissions on all of the stored procedures that it needs to call.
This works fine except for one of my stored procedures which is building up some dynamic SQL and calling sp_executesql.
The dynamic sql looks sort of like this:
SET @SQL = N'
SELECT *
FROM dbo.uvView1
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'
EXEC sp_executesql @SQL
The users in this role are failing to call the stored procedure. It gives the following error which is sort of expected I suppose:
The SELECT permission was denied on the object 'uvView1', database 'Foobar', schema 'dbo'.
Is there a way I can have my users successfully execute this proc without giving the role permissions to all of the views in the dynamic SQL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的。
将 EXECUTE AS CALLER 子句添加到过程中，然后对存储过程进行签名并为签名授予所需的权限。这是 100% 安全、可靠且防弹的。请参阅使用证书签署程序。
Yes.
Add an EXECUTE AS CALLER clause to the procedure, then sign the stored procedure and give the required permission to the signature. This is 100% safe, secure and bullet proof. See Signing Procedures with Certificates.
您可以使用模拟到具有所需权限的另一个ID吗？
Can you use impersonation to another ID with the required permissions?
否。有什么方法可以将其更改为不使用动态 SQL？
No. Is there any way you can change it to not use dynamic SQL?