Post reply

sp_changedbowner error - sa is already db user

  • 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

  • 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

  • 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'.

  • 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

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

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