MS SQL Error: THE EXECUTE PERMISSION WAS DENIED ON THE…


WHAT

When trying to execute a stored procedure in MS SQL Server  with a user-defined type as a parameter you receive an error that reads something like:

“THE EXECUTE PERMISSION WAS DENIED ON THE…”

WHY

The reason you need to supply permission for your defined type is because technically you are trying to access say a “table” (your user defined type) that is outside of the proc.  To get access to this “table” you need permission for your user just like you would any table.

SOLUTION

The solution is to grant permission to the type.  Be sure to include the “TYPE::” in the object name you are granting permission to.  See below for an example.


For more on this please see the following stackoverflow post:
Table valued parameter in a stored procedure gets execute permissions denied error

Leave a comment