使用动态 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"、架构dbo"的 SELECT 权限被拒绝.

有没有办法让我的用户成功执行这个过程，而无需为动态 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屋！