BUG: sp_addtype Does Not Allow Owner Qualified Object Names

• Microsoft SQL Server version 7.0

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.

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

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          : SSrvAdmin SSrvTran_SQL kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 2, 1999