2

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?

5

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
  • Thank you David. – McNets Oct 18 at 17:11
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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.