Unable to change the ContinuumDB database owner using the stored procedure sp_changedbowner

[Product_Support]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support Services for assistance.

Issue

When running the stored procedure sp_changedbowner to change the dbowner to "andover97", the following error message is received: "The proposed new database owner is already a user or aliased in the database".

Product Line

Andover Continuum

Environment

• Continuum
• Microsoft SQL Server 2000
• Microsoft SQL Server 2005
• Microsoft SQL Server 2008
• (Keywords: drop user )

Cause

The Andover97 user already has access to the database through an existing alias or security account within the database.

This can occur when the DB is not restored properly to the SQL server. The database may have been detached and then attached again, which is not the preferred method of restoring a ContinuumDB.

Resolution

Under the ContinuumDB there is a "security" and then "users" folder. Check to see if the Andover97 user is in this folder. Continuum does not put this user in this folder. If it is there, then it will need to be dropped from the database before trying to change the dbowner. Do the following:

1. Do a complete full backup of your ContinuumDB.
2. Run the following SQL script to drop the user from the ContinuumDB.
   

   USE [ContinuumDB]
   GO
   DROP USER [Andover97]
   GO

3. Re-run the stored procedure to change the db owner below.
   

   USE [ContinuumDB]
   GO
   sp_changedbowner 'andover97'
   GO

To minimize the chance of this occurring again, the correct procedure for moving or restoring a Continuum database is to do the following:

1. Create a new ContinuumDB with Continuum's Database Initialization. This creates the DB, SQL jobs, stored procedures, adds Andover97 as an admin and a lot of behind the scenes security settings.
2. Restore a previous backup over top of the ContinuumDB created in step 1.
3. Change the db owner with the above query using the sp_changedbowner stored procedure.
4. Re-initialize the Cyberstation into the Database using Continuum's Database Initialization.