UDT Execute Permission

The required cases for execute permission on UDFs and UDPs are pretty understandable, however the situation for a UDT is not quite so clear-cut.  What does “Execute” really mean in the context of a UDT anyway?

Simply put, execute permission is required any time any code in your UDT needs to run.  This sounds basic, but UDT serialization will run your code implicitly without direct invocation in a number of scenarios.  An additional wrinkle is that in a Format.Native udt, the serialization code is provided to you by the server, however it still requires execute permission to run.

For example, doing a direct selection from a table does not require execute permission because the udt gets returned to the client as bytes without having to instantiate the udt.  However, selecting into a local variable will instantiate the udt into memory, which requires deserializing the udt and hence execute permission.

More actions that serialize or deserialize UDTs behind the scenes:

- Selecting a UDT over a Distributed Query in a linked server environment

- Implicit Conversion to a UDT from string or bytes

- Comparison between a UDT and binary where the UDT is not explicitly cast to bytes (ie, WHERE (convert(varbinary(8), udt) = 0xDEADBEEF will work, but WHERE udt = 0xDEADBEEF will actually serialize the bytes into a UDT before comparing)

- Accessing a UDT field

- Running DBCC CheckTable on a table that contains a udt column

-- Steven Hemingray