9

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.

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.

Any thoughts?

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

StuartLC
  • 104,537
  • 17
  • 209
  • 285
Josh Russo
  • 3,080
  • 2
  • 41
  • 62

2 Answers2

12

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]

Edit

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.

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

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.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • If I can use the TVP in the body of the stored proc, doesn't that mean that I already have EXECUTE? – Josh Russo May 15 '14 at 13:45
  • According to your link it seems like I did already have execute permission: `To declare a table variable that uses a user-defined table type, EXECUTE permission is required on that user-defined table type.` – Josh Russo May 15 '14 at 13:46
  • Correct, it's on the creation of the stored proc – Josh Russo May 15 '14 at 14:02
  • @JoshRusso I believe your DBO may need to grant you `GRANT REFERENCES ON TYPE::schema.tabletype` in order to be able to use it in a `PROC`. – StuartLC May 15 '14 at 15:53
  • 1
    The Grant References did it! Thank you! – Josh Russo May 19 '14 at 11:55
0

Right click on your table valued funtion that you want to grant access. Take the properties from the context menu.

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

2) Browse to the user name (SQL or Windows).

3) Select that user.

4) Under Explicit Permissions section at the bottom select Grant for Control Permissions click OK.

5) click OK

enter image description here

Yawar Murtaza
  • 3,655
  • 5
  • 34
  • 40