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

asp.net security sql-server-2008 stored-procedures user-defined-types
Janez picture Janez · Jul 29, 2011 · Viewed 48k times · Source

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

mccow002 picture mccow002 · Nov 2, 2011

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

Related questions

What are all the user accounts for IIS/ASP.NET and how do they differ?

Under Windows Server 2008 with ASP.NET 4.0 installed there is a whole slew of related user accounts, and I can't understand which one is which, how to they differ, and which one is REALLY the one that my app runs under. …

Read More
X-Frame-Options Allow-From multiple domains

I have an ASP.NET 4.0 IIS7.5 site which I need secured using the X-Frame-Options header. I also need to enable my site pages to be iframed from my same domain as well as from my facebook app. Currently I have …

Read More
ASP.NET Identity's default Password Hasher - How does it work and is it secure?

I am wondering wether the Password Hasher that is default implemented in the UserManager that comes with MVC 5 and ASP.NET Identity Framework, is secure enough? And if so, if you could explain to me how it works? IPasswordHasher interface …

Read More