Permissions required for user defined table types in Microsoft SQL Server
I've been working in and around SQL for over 3 decades.
I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone).
I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects.
Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.
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]
