PRB: Changing Ownership of a Database

ID: Q78943

The information in this article applies to:

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 query words: sp_changedbowner() 


Keywords          : kbother SSrvGen SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 11, 1999