FIX: sp_addtype Does Not Allow Owner Qualified Object Names

Article translations Article translations
Article ID: 234256 - View products that this article applies to.
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)
Expand all | Collapse all

SYMPTOMS

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.
-or-
Msg 15006, Level 16, State 1
'dbo.mytype' is not a valid name since it contains invalid characters.

WORKAROUND

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.

STATUS

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.

MORE INFORMATION

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.

Properties

Article ID: 234256 - Last Review: October 21, 2013 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB234256

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com