SQL: Assign permissions to the table type not directly to the object name

When you use table types in SQL Server, you need to assign permissions on them, and usually EXECUTE permissions. On one of my mailing lists, I saw a posting about how the user was trying to assign permissions to a table type  (for use as a table-valued parameter), and was getting this error:

Msg 15151, Level 16, State 1, Line 11 Cannot find the object, because it does not exist or you do not have permission.

He was wondering if permissions on table types didn’t work properly in Azure SQL Database, which was where he was executing the command.

The problem with this is that you need to assign the permission using the TYPE moniker, not directly to the object name. Let’s see an example:

If I execute the following code:

I’ll see this error:

What I needed to do instead, is to execute this code:

That will work just fine.

Umachandar Jayachandran (UC) from Microsoft pointed out to us that this is because objects and types are in different namespaces within SQL Server and the default namespace is OBJECT (ie: tables, views, stored procedures), not the namespace that has types.

2018-05-14