FIX: sp_addtype Does Not Allow Owner Qualified Object Names

This article was previously published under Q234256
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55748 (SQLBUG_70)
If you use the sp_addtype stored procedure to create a user-defined type, the stored procedure does not accept an owner-qualified type name. Instead, the object will be owned by the user issuing the command. After the type has been created, the user cannot be removed from the database until all objects owned by the user have also been dropped. This may require dropping any tables whose definition included the user-defined type.

An attempt to qualify a type with an owner may result in one of the following errors, depending on the syntax used:
Msg 15005, Level 16, State 1
'[dbo].[mytype]' is not a valid name since it begins with an invalid character.
Msg 15006, Level 16, State 1
'dbo.mytype' is not a valid name since it contains invalid characters.
When creating the new type, connect as the desired type owner. Alternatively, if your login is a member of the sysadmin fixed server role, you can issue a SETUSER username command before executing sp_addtype.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
A common situation where you may want to create a user-defined type as a different owner is in a development environment. In this situation, many different users may have the db_owner role and would want to create the objects as database owner (dbo), so that their user can be dropped from the database when the development process is completed.
permission security

Article ID: 234256 - Last Review: 10/21/2013 22:26:36 - Revision: 2.0

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix KB234256