The EXECUTE permission is denied on the user-defined table types?

Question

I have a question about User-Defined Table Types in SQL Server 2008.

For the need of one of the ASP.NET application we defined our own table-types on SQL Server 2008 to use them as parameters in the stored procedures (when executing sql command in ASP.NET application we pass DataTable object as parameter for stored procedure see here for an example)

The problem is that when we run Sql command (execute stored procedure) from ASP.NET we get an error:

The EXECUTE permission was denied on the object 'ourTableType', database 'ourDatabase', schema 'ourSchema'.

Why is that so? Why do we need to set permission on user-defined table types? Why is not enough to have permission set just on stored procedure that uses it? And if we have to set it no matter what, why there is no EXECUTE permission type to set in properties window whatsoever (I can see only Control, References, Take Ownership, View Definition)?

What I also don't understand is that setting permission to Control in properties window solves the problem and the stored procedure runs without problems.

Answer 1

I really hope you've solved this by now, seeing as the question is almost 4 months old, but in case you haven't, here's what I think is the answer.

GRANT EXEC ON TYPE::[schema].[typename] TO [User]
GO

Answer 2

If your stored procedure is using dynamic sql, meaning the @sql is generated and then executed via exec @sql, you will need permission granted on the underlying tables.

One work-around is to modify to stored procedure to run as a different user. If you make it run as SELF, it will be ran underneath the creator of the stored proc, which is extremely dangerous. Still, if you have no other option:

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS SELF

Answer 3

In SSMS you need to click the "View schema permissions" link before clicking the "Search" button.