What permission do I need to use the value of the SQL Server Table Valued Parameter (TVP) as the stored parameter proc?

Question

I am using SQL Server 2008 R2 and I created a TVP that I want to use as a parameter for a saved process, but I get a message that it cannot be found or that I do not have permission.

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

Any thoughts?

Edit: To clarify, the error I'm getting is to create a proc stored procedure

Answer 1

In order for the caller to use the PROC parameter with the table parameter, you must (unintuitively) grant permissions to execute like TVP those who call PROC ie

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

Edit

I believe that I was able to replicate the problem, namely working with the minimum set of permissions granted to the user. The life step for the owner of the DBO or Schema of your TVP is to give you the following access to it so that I can use it in PROC (without this access, I could declare a free TVP variable, but not use it in PROC).

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

Grant link here (Obviously, you will also need CREATE PROCEDURE permission, as well as appropriate access to any objects used in PROC)

PROC consumers must also have GRANT EXECUTE approval for Proc and type in accordance with the original answer.

Answer 2

Right click on your table value that you want to provide. Take properties from the context menu.

1) Click the "Permissions" tab on the right.

2) Find the username (SQL or Windows).

3) Select this user.

4) In the "Explicit permissions" section at the bottom, select "Grant management permissions", click "OK."

5) click "OK"