Search code examples
sqlsql-serversql-server-2008permissionstable-valued-parameters

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

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

Solution

  • 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.