sp_changedbowner error - sa is already db user

• jeff451

  SSChasing Mays

  Points: 636

  More actions

  December 19, 2002 at 7:59 am

  #63235

  I've created a fine mess and I am not sure how to resolve it. Here is the scenario.

  1. Restored 3 production databases to a new test server

  2. The database owner s now SCS\BACKUP

  3. In 2 of the databases I ran sp_dropuser for all of the users in the application security table (username is the same as the SQL login name)

  4. Ran sp_addlogin and sp_grantdbaccess for all of the application users in 2 of the databases. This worked fine - users can now login to the test server.

  5. I cannot create new users on the TEST server because the application only allows the dbowner to add users.

  6. sp_changedbowner does not wrok in these 2 databases because sa is now a user in the database (courtesy of sa being in the application security table and my nifty cursor that ran sp_grantdbaccess for every record in the application security table). sp_changedbowner reports an error "The proposed new database owner is already a user in the database"

  My problem is that I cannot drop the sa user from the database because dbo owns all of the objects. This is a 60GB database so I would rather not do the restore again.

  Any ideas would be appreciated.

  Jeff451SQL Guru wannabe

• Steven.

  SSCrazy Eights

  Points: 9806

  More actions

  December 19, 2002 at 8:24 am

  #444813

  If you want to create users in the database, you may be able to use the sp_addalias stored procedure to alias an account to dbo.

  Steven

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  December 19, 2002 at 8:41 am

  #444816

  Try to change database owner to any one of logins (not sa) and change the database owner back to 'sa'.

• jeff451

  SSChasing Mays

  Points: 636

  More actions

  December 19, 2002 at 11:32 am

  #444849

  Thanks for those suggestions. Here is what I did to fix the issue. I don't recommend this solution for production data. So far it seems to be working fine in test.

  sp_configure 'allow updates','1'

  RECONFIGURE WITH OVERRIDE

  update sysdatabases

  set sid = 0x01 where name = 'name'

  sp_configure 'allow updates','0'

  RECONFIGURE WITH OVERRIDE

  name is the name of the database that you want to change to the owner to sa

  I would not normally update the system tables directly but it seemed like my only option in this instance.

  Jeff451SQL Guru wannabe