使用动态SQL的存储过程中的SQL Server权限 [英] SQL Server Permissions on Stored Procs with dynamic SQL

问题描述

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

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.

除了我的存储过程之一是建立一些动态SQL并调用sp_executesql。

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

动态sql看起来像这样：

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:

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

有没有一种方法可以让我的用户成功执行此proc，而无需将角色授予动态SQL中的所有视图权限？

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?

推荐答案

是。

将EXECUTE AS CALLER子句添加到过程，然后对存储过程进行签名，并授予签名所需的权限。这是100％安全，可靠和防弹的。请参见带有证书的签名过程。

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.

这篇关于使用动态SQL的存储过程中的SQL Server权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！