Permissions required for user defined table types in Microsoft SQL Server

This has bitten me so many times, I’m putting this here where I can find it in the future. You might want to bookmark this page :)

I rely heavily on stored procedures for interaction both internally and externally. The improve performance and efficiency I’ve created many table types, most notably one for isbns. It looks like this:

create type dbo.ISBN as table(
    ISBN char(13) not null
    primary key clustered (ISBN asc)
) with (ignore_dupe_Key=off))

Let’s use this in a sample procedure

create procedure inventory.CheckISBNQty
(@ISBNs dbo.ISBN readonly)
with execute as owner
as
.....

We have a function app, [func-InventoryProcess-prod] that will be calling this procedure so naturally you would grant it the ability to execute the function.

grant exec on inventory.CheckISBNQty to [func-InventoryProcess-prod]

When the function app tries to run the stored procedure, you will get an error that you cannot execute dbo.ISBN. To solve this problem, you need to add execute permissions on the table object to the app.

grant execute on type::dbo.ISBN to [func-InventoryProcess-prod]
or
grant execute on type::[dbo].[ISBN] to [func-InventoryProcess-prod]

Permissions required for user defined table types in Microsoft SQL Server

Comments

More from this blog

Analysis Services models in Azure Fabric -- METADATA$ROW_ID gotcha

Azure Data Factory utcNow() nuances

Microsoft SQL Server permission chaining

Using a stored procedure as a source for PowerBI Paginated reports

Command Palette

Comments

More from this blog