You are not able to reference a user defined data type that is defined in model database in tempdb

Consider the following scenario:

  • You create a user-defined data type in the model database.

  • A user who is a not a system administrator tries to use this data type to create a temporary table in the model database.

In this scenario, the user will get the following error message

Msg 15247, Level 16, State 4, Server <Server name>, Line 1

User does not have permission to perform this action.

Symptoms

The Books Online topic for CREATE TABLE has the following note under Permissions section:

If any columns in the CREATE TABLE statement are defined to be of a CLR user-defined type, either ownership of the type or REFERENCES permission on it is required. 

This note is true not only for CLR data types but also for any user-defined data type (UDT). Refer to the topic titled “Using User-defined Types Across Databases” in the SQL Server Books Online regarding the behavior of CLR data types. 

NOTE: You encounter this problem only when explicitly creating tables and not when implicitly creating tables using SELECT INTO statements. 

Cause

Grant permissions for user defined data type to the appropriate users as suggested in the Cause section. You can also workaround the problem by using one of the following methods.    

Method 1: Grant REFERENCES permission to the public user in the model database.

For example:

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×