21

I get the following error when calling a stored procedure that has a table valued parameter as one of the parameters

The EXECUTE permission was denied on the object 'ValidationErrors'

ValidationErrors is a TVP created with the following statement: 

CREATE TYPE [dbo].[ValidationErrors] AS TABLE(
    [ErrorMessage] [varchar](255) NOT NULL
)

The user executing the stored procedure has execute privileges on the stored procedure. However, I still get the above error. Any ideas?

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
arunpereira
  • 582
  • 5
  • 13

3 Answers3

38

I think you may also need to grant the user permissions to the type.

References for GRANTing permissions to types:
SQL 2005
SQL 2008

Update:
Re: why you have to grant permissions on the type when you have permissions on the sproc. I don't know the definitive reason, but BOL says:

Unlike user-defined types created by using sp_addtype, the public database role is not automatically granted REFERENCES permission on types that are created by using CREATE TYPE. This permission must be granted separately.

Update 2: To GRANT EXECUTE permissions, you'd run this in SSMS:

GRANT EXECUTE ON TYPE::dbo.ValidationErrors TO SomeUser;
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • I granted the "control" permission to the object and it seems to be working fine now. Simply adding "references" and "view definition" did not help. Personally, I don't understand the requirement since it is a type and generally when stored procedure execution is granted, referenced objects don't need specific permissions to be granted. However, this might have something to do with the fact that the user is in the "public" role. Any thoughts? – arunpereira Feb 11 '10 at 12:27
  • Did you try granting EXECUTE permissions (instead of control)? Might be worth trying that before resorting to control – AdaTheDev Feb 11 '10 at 12:33
  • 2
    I saw no place to grant execute from the sql management studio. I only saw the ones I listed above. Do I have to use t-sql to grant this? – arunpereira Feb 11 '10 at 18:50
  • @chiefbrownbotom - yes, I've updated my answerd, see Update 2 – AdaTheDev Feb 11 '10 at 22:55
  • 4
    @chiefbrownbotom: Stored procedures can get around the need to set permissions on a *physical table or view*, but a TVP is not just used *in* the stored procedure, it is passed *to* the stored procedure. In other words, the TVP instance needs to be created before the SP is ever called; hence, permissions still need to be granted on the TVP itself. – Aaronaught Feb 11 '10 at 23:02
  • 1
    We usually grant EXECUTE and VIEW DEFINITION to [public] for table types. Unless you really don't random strangers to see that table of ints! – Peter Radocchia Feb 11 '10 at 23:07
3

As @chiefbrownbotom says, the reason why you need execute permissions on the table type is that the table is created before (and therefore outside of) the call to the proc. To illustrate this run a SQL Profiler trace and call your proc. You will see something like this which might surprise you...

DECLARE @p1 TABLE AS YourTableType
INSERT INTO @p1 (col1, col2, col3) VALUES ('val1','val2','val3')
INSERT INTO @p1 (col1, col2, col3) VALUES ('val1','val2','val3')
...
EXEC usp_YourStoredProc @p1
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
Ben Seaman
  • 31
  • 1
1

Grant Control on TYPE::schema.mytabletype to RoleOrMember

This worked for me; many thanks to @chiefbrownbotom in the comment above.

Steve G
  • 993
  • 1
  • 7
  • 14