使用SQL Server表值参数(TVP)作为存储的proc参数需要什么权限? [英] What permission do I need to use an SQL Server Table Valued Parameter (TVP) as a stored proc parameter?

问题描述

我正在使用SQL Server 2008 R2，并且创建了一个TVP，我想将该TVP用作存储proc的参数，但是我收到一条消息，提示找不到它或我没有权限

I'm using SQL Server 2008 R2 and I've created a TVP that I want to use as a parameter to a stored proc but I get a message saying that it can't be found or I don't have permission.

我可以在脚本或存储的proc的主体中使用TVP，但是当我尝试将其用作参数时，会出现错误.

I can use the TVP in a script or in the body of the stored proc, but when I try to use it as a parameter I get the error.

有什么想法吗?

编辑:为澄清起见，我收到的错误是有关创建存储过程的信息

For clarification, the error I'm getting is on the creation of the stored proc

推荐答案

为使调用者将PROC与表值参数一起使用，您需要(不直观地)

In order for a caller to use a PROC with a table valued parameter, you'll need to (unintuitively) grant execute permissions on the TVP type to those calling the PROC i.e.

GRANT EXECUTE ON TYPE::[schema].[MyTVP] to [SomeRole]

修改

我相信我能够复制该问题，即通过向用户授予最小权限集来解决.至关重要的步骤是让TVP的DBO或模式所有者授予您对其的以下访问权限，以便能够在PROC中使用它(没有此访问权限，我就可以声明该变量的松散变量). TVP类型，但不能在PROC中使用.

I believe I was able to replicate the issue, viz working from a minimal set of permissions granted to a user. The vital step is for the DBO or Schema Owner of your TVP to grant you the following access to it, in order to be able to use it in a PROC (without this access, I was able to declare a loose variable of the TVP type, but not use it in a PROC).

GRANT REFERENCES ON TYPE::[schema].[MyTVP] to YOURROLE -- Or User.

在此处授予引用 (显然，您还需要CREATE PROCEDURE权限，以及对PROC中使用的任何对象的相关访问权限)

Grant Reference here (Obviously you'll also need CREATE PROCEDURE permission, plus relevant access to any objects used in the PROC)

根据初始答案，PROC的消费者还需要对Proc和类型具有GRANT EXECUTE权限.

Consumers of the PROC will need to also have the GRANT EXECUTE permission on the Proc and on the Type as per the initial answer.

这篇关于使用SQL Server表值参数(TVP)作为存储的proc参数需要什么权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！