Use a user data type (UDT) in temp table

Every now and then someone wants to use a user-defined data type (UDT) from a database in a temp table.

Initially trying it gives the error:
CREATE TABLE #Test (TestField GrumpyDBA)
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type GrumpyDBA

To enable the datatypes in temp tables, create them in TEMPDB ... and assuming it's not just for a one off script, also in MODEL (so they are rebuilt in tempdb on each startup).

CREATE TYPE GrumpyDBA FROM INT NOT NULL

So far, so obvious, but the issue that now crops up is the following error when a user tries to create the table again:

Msg 15247, Level 16, State 4, Line 2
User does not have permission to perform this action.

It's quite easy to give up at this point, but it's actually an easy update to fix this as well, just run this in TEMPDB (and Model)

GRANT REFERENCES ON TYPE::dbo.GrumpyDBA TO public

And you should now find you can use the UDT in temp tables from the original database.