File this one under "these aren't the permissions you're looking for"
As noted in http://billfellows.blogspot.com/2010/06/t-sql-tuesday-007-summertime-in-sql.html we have begun using TVPs at my work and they've been working out well for us. One thing I noticed was that I didn't have permission to actually use them in environments where I was not an admin. When I attempted to use a user defined table type, SQL Server would bark with "The EXECUTE permission was denied on the object 'FOO', database 'BAR', schema 'dbo'."
Executing the following statement
DECLARE @t1 dbo.CONTRIVED_EXAMPLE
Msg 229, Level 14, State 5, Line 1 The EXECUTE permission was denied on the object 'CONTRIVED_EXAMPLE', database 'TSQL_TUESDAY_007', schema 'dbo'.
Fair enough, I updated all my scripts to add explicit execute permissions to our team so that we could use them and of course didn't test because I've assigned execute permissions a bajillion times before. Yup, it even says so in BOL "To declare a table variable that uses a user-defined table type, EXECUTE permission is required on that user-defined table type." They deployed my code while I was out of town and BOOM! it defecated all over itself.
What I supplied was
GRANT EXECUTE ON dbo.CONTRIVED_EXAMPLE TO [Domain\DevTeam]
and what I received was
Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'CONTRIVED_EXAMPLE', because it does not exist or you do not have permission.
That syntax is F'ing right, here's my proc grant statement
GRANT EXECUTE ON dbo.EchoBack TO public
The user defined type name is correct
SELECT ST.* FROM sys.types ST WHERE ST.is_user_defined = 1
I tried it without the schema to no avail, what the hell... It turns out that the syntax wasn't entirely correct. Final BOL reference shows the syntax is actually
GRANT EXECUTE ON TYPE::dbo.CONTRIVED_EXAMPLE TO [Domain\DevTeam]
I also learned (Grant transact SQL link) that I can update my proc/function statements to be more explicit
GRANT EXECUTE ON OBJECT::dbo.EchoBack TO public
http://msdn.microsoft.com/en-us/library/bb522526.aspx BOL: User-Defined Table Types
http://msdn.microsoft.com/en-us/library/ms187965.aspx BOL: Grant (Transact SQL)
http://msdn.microsoft.com/en-us/library/ms191291.aspx BOL: Permissions
http://msdn.microsoft.com/en-us/library/ms174346.aspx BOL: GRANT Type Permissions (Transact-SQL)