ID Number: Q78943
1.10 1.11 4.20
OS/2
Summary:
SYMPTOMS
SP_CHANGEDBOWNER cannot be used to transfer ownership of a database
to a user who owns objects within the database.
CAUSE
The new owner of the database must have a login ID on SQL Server,
but must not have a user name or alias in the database.
WORKAROUND
To assign the ownership of the database to a person with a user name
or alias in that database, the person's user name and aliases must
be dropped before changing ownership. However, a user owning
objects within the database cannot be dropped from it and any
attempt to do so will result in the following error message,
followed by the name and type of the objects:
You cannot drop user because he or she owns objects in database
To work around this problem, give the user another login ID to SQL
Server and assign the ownership of the database to the new login ID
using SP_CHANGEDBOWNER.
An alternative is to BCP out the data from the tables owned by the
user, drop the tables, drop the user/aliases, transfer the
ownership of the database to the user, and then have the user
re-create the tables as a database owner (DBO) and BCP in the data.
Additional reference words: 1.10 1.1 1.11 4.2 4.20 sp_changedbowner()