Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

Symptoms

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.

↑ Back to the top

Cause

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. 

↑ Back to the top

Resolution

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:

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL ;
go
GRANT REFERENCES ON TYPE::dbo.udt_money TO public

NOTE: Before using this method carefully evaluate the security implications since this permission gets carried over to every new database. 

 

Method 2: If you do not want every new database to retain the definition and permissions for this user- defined data type, you can use a startup stored procedure to create and assign the appropriate permissions only in tempdb database.

For example:    

USE master
go
CREATE PROCEDURE setup_udt_in_tempdb
AS
EXEC ( 'USE tempdb;
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GRANT REFERENCES ON TYPE::dbo.udt_money TO public;')
go
EXEC sp_procoption 'setup_udt_in_tempdb' , 'startup' , 'on'
go

 

Method 3: Instead of using temporary tables, consider using table variables when you need to reference user-defined data types for temporary storage needs. For table variables to reference user-defined data types, you do not need to explicitly grant permissions for the user-defined data type.

↑ Back to the top

Keywords: vkball, kb

↑ Back to the top

Article Info
Article ID : 2009217
Revision : 1
Created on : 1/8/2017
Published on : 1/6/2010
Exists online : False
Views : 164