HOW TO: Change the Owner of a User-Defined Data Type That Is in Use in SQL Server 2000

This article describes how to change the owner of a user-defined data type that
is in use.

To change the owner of a user-defined data type, you must drop
and then re-create the data type in the context of the owner you want. However,
if the user-defined data type is in use, you will receive the following error
message when you try to drop the data type:

Server: Msg 15180, Level 16, State 1, Procedure sp_droptype,
Line 32 Cannot drop. The data type is being used.Steps to Change the Owner of a User-Defined Data Type That Is
in Use

To change the owner of a user-defined data type that is in use, follow these
steps:

1. Script out the definition of the user-defined data type with SQL Server
   Enterprise Manager (SEM).
2. Expand your SQL Server, expand Databases, expand your database, and
   then expand User defined data types.
3. In the right pane, right-click the data type you want, and then click All
   Tasks.
4. Click Generate SQL Script, and then click OK.
5. Select the file name and location in which you will store the script.
6. Determine all the tables or columns that are using the user-defined data
   type by using the following code (replace the database name and data type with
   your database name and data type):

   USE database nameSELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DOMAIN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE DOMAIN_NAME = 'data type'

   NOTE: In this context, DOMAIN_NAME
   represents the user-defined data type.

7. For each table that is using the user-defined data type, change the column
   data type to the base data type by using an ALTER TABLE statement. For example
   to change mytable..mycolumn to the datetimebase data type, use:

   ALTER TABLE mytable ALTER COLUMN mycolumn datetime

8. Drop the user-defined data type.
9. Re-create the user-defined data type by using the script you saved in step 4
   under the context of the owner you want.
10. Change all the columns you want back to the user-defined data type by using
    an ALTER TABLE statement as in step 7.