PRB: Changing Ownership of a Database

  - Microsoft SQL Server version 4.2 for OS/2

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

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.