EXECUTE permission rejected in custom table types?

Question

I have a question about Custom Table Types in SQL Server 2008.

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

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

EXECUTE permission was denied for object 'ourTableType', database 'ourDatabase', schema 'ourSchema'.

Why is this so? Why do we need to set permissions on custom table types? Why is it not enough to have permission set only on a stored procedure that uses it? And if we must install it regardless of why there is no EXECUTE permission type for setting in the properties window at all (I can only see Control , References , Take Ownership , View Definition )?

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

Answer 1

I really hope you have resolved this now, because the question has been around for almost 4 months, but if you haven’t done this, that’s what I think is the answer.

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

Answer 2

If your stored procedure uses dynamic sql, which means that @sql generated and then executed using exec @sql , you will need the permissions granted in the base tables.

One workflow is to modify the stored procedure to run as another user . If you run it as SAMO, it will be launched under the creator of the stored procedure, which is extremely dangerous. However, if you have no other option:

 CREATE PROCEDURE dbo.usp_Demo WITH EXECUTE AS SELF