Microsoft KB Archive/234256

= FIX: sp_addtype Does Not Allow Owner Qualified Object Names =

Article ID: 234256

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q234256



BUG #: 55748 (SQLBUG_70)



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 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.

Additional query words: permission security

Keywords: kbbug kbfix KB234256

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.