存储过程的SQL Server权限与动态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.

我有一个具有应用程序角色的数据库。角色成员都属于Active Directory中的组。我没有赋予角色权限以从表中选择，而是赋予角色对其需要调用的所有存储过程的执行权限。

This works fine except for one of my stored procedures which is building up some dynamic SQL and calling sp_executesql.

这工作正常，除了我的一个存储过程，它正在构建一些动态SQL并调用sp_executesql。

The dynamic sql looks sort of like this:

动态sql看起来像这样：

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'.

对象'uvView1'，数据库'Foobar'，架构'dbo'上的SELECT权限被拒绝。

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?

有没有办法让我的用户成功执行此proc而不赋予动态SQL中所有视图的角色权限？

3 个解决方案

#1

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.

向过程添加EXECUTE AS CALLER子句，然后对存储过程进行签名并为签名提供所需的权限。这是100％安全，安全和防弹。请参阅使用证书签名过程。

#2

Can you use impersonation to another ID with the required permissions?

您是否可以将模拟用于具有所需权限的其他ID？

SET @SQL = N'
EXECUTE AS USER = ''TrustedUser'';
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL

#3

No. Is there any way you can change it to not use dynamic SQL?

没有。有什么办法可以改变它不使用动态SQL吗？

#1