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?
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;
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
Grant Control on TYPE::schema.mytabletype to RoleOrMember
This worked for me; many thanks to @chiefbrownbotom in the comment above.
