sp_changedbowner error - sa is already db user

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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