Permissions using a TYPE as a Table Valued Parameter

Question

I'm developing a .Net App that calls an stored procedure that have a table valued parameter declared as a TYPE READONLY.

First time I tried to call the SP I received the next error:

Msg 229, Level 14, State 5, Line 1
The EXECUTE permission was denied on the object 'TYPE_OBJ', database 'MY_DB', schema 'dbo'.

After granting EXECUTE permission to the user it works fine.

But I can't find in MS-DOCS neither in CREATE TYPE nor in Use table-valued parameters any reference to the permissions needed to use it as a parameter.

Where can I find information about the necessary permissions to use a TYPE as a parameter?

Answer 1

Where can I find information about the necessary permissions to use a TYPE as a parameter?

I've submitted a PR against the doc page to get the requirements documented.

https://github.com/MicrosoftDocs/sql-docs/pull/3351

Here's a simple repro:

create type dbo.tt as table(id int)

go

create procedure dbo.ptt @tt tt readonly
as
select * from @tt

go

create user joe without login



grant references on type::dbo.tt to joe
grant execute on dbo.ptt to joe

go

execute as user='joe'

  declare @t tt
  exec ptt @t 
  --The EXECUTE permission was denied on the object 'tt', database 'a', schema 'dbo'.

revert

go

grant execute on type::dbo.tt to joe

go

execute as user='joe'

  declare @t tt
  exec ptt @t 
  --no error

revert

Answer 2

I believe you just need to give permissions to the REFERENCES.

The REFERENCES permission is required on the user-defined table type when the table type is a parameter in a routine...