111

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.

Janez
  • 2,336
  • 5
  • 25
  • 37
  • 4
    possible duplicate of [Table valued parameter in a stored procedure gets execute permissions denied error](http://stackoverflow.com/questions/2244217/table-valued-parameter-in-a-stored-procedure-gets-execute-permissions-denied-erro) – Damien_The_Unbeliever Jul 29 '11 at 10:49
  • thank you! I've searched but clearly not good enough:/ – Janez Jul 29 '11 at 11:43
  • 1
    Try putting `AS dbo` at the end. Like this: `GRANT EXEC ON TYPE::[schema].[typename] TO [User] AS dbo`. Worked for me. – Jonathan Mar 15 '13 at 05:09
  • Possible duplicate of [Table valued parameter in a stored procedure gets execute permissions denied error](https://stackoverflow.com/questions/2244217/table-valued-parameter-in-a-stored-procedure-gets-execute-permissions-denied-err) – LCJ Feb 19 '19 at 22:54

3 Answers3

248

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
mccow002
  • 6,754
  • 3
  • 26
  • 36
  • 12
    My 2 cents: Depending on your connection authentication mechanism, you may have to grant exec to Public group. So your grant would look like this: GRANT EXEC ON TYPE::[schema].[typename] TO [Public] GO – Sudhanshu Mishra Jun 02 '15 at 23:48
  • 1
    @dotnetguy thank you very much, none of the solutions worked for me but yours. – Mazen el Senih Dec 10 '18 at 08:38
3

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
rkw
  • 7,287
  • 4
  • 26
  • 39
  • 1
    Thx for pointing this out. But stored procedure does not have any dynamic sql in it. Only general `INSERT INTO` and ´UPDATE´ table statements for which this user has all permissions he needs. Also this user/login is specially reserved/created for this ASP.NET application to be able to connect to this database and only execute stored procedures (not create etc, creator is always `'sa'`). – Janez Jul 29 '11 at 11:36
  • Thanks, that was the problem for me. Other readers with this problem can refer to [SQL Server Permissions on Stored Procs with dynamic SQL](https://stackoverflow.com/q/4081236/1026) for more tips. – Nickolay Feb 27 '19 at 09:14
0

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

Paul Chen
  • 1,873
  • 1
  • 16
  • 28