Post reply

database ownership - how do I change it

  • November 19, 2001 at 7:52 am

    #157749

    I am in SQLServer 7.0 SP3 on NT 4.0 service pack 6a.

    I have just moved master, model, msdb, Northwind, pubs and tempdb from one drive to another. I looked at each database in turn upon completion of the task and found that all bar master and tempdb were now owned by me rather than sa!

    I then tried to use sp_changedbowner to change ownership back to sa (our policy is that no individual NT user should own the databases). When I try on msdb it says

    Server message 15110, Level 16, State 1, Procedure sp_changedbowner, line 46

    The proposed new database owner is already a user in the database

    I then tried it on model and it says

    Server message 15109, Level 16, State 1, Procedure sp_changedbowner, line 21

    Cannot change the owner of the master database

    I know that I have already managed to change the ownership of model by moving it. I am confused and somewhat vexed!. Can anyone out there tell me what I am doing wrong?

  • November 19, 2001 at 9:37 am

    #420186

    For msdb, try removing the user.

    For model, not sure. Can you change it as "sa"?

    Steve Jones

    steve@dkranch.net

  • November 19, 2001 at 12:23 pm

    #420205

    You can use either query analyzer or enterprise manager to do it but you have to drop the user from the database, and then issue the sp_changedbowner.

    Good luck

    Tom

  • November 20, 2001 at 7:10 am

    #420235

    Cheers, I did it, then discovered I had to restore the database from another server which already had the dbowner as sa, so I needn't have bothered - but hey, it's all good(?) experience! As was trying to get the database to restore over the network. It has both publisher and distributer databases on it so when I restored Master I ended up with them both suspect because neither of them were there! I had a real job to get them back up and running, including having to delete records out of sysdatabases! Lukily I have the luxury of not having anyone jumping up and down waiting for this database until January! Now I just have to check that I haven't messed anything up in my attempts to get the dratted thing restored.

  • November 20, 2001 at 9:58 am

    #420238

    Glad its working and thanks for the update.

    Steve Jones

    steve@dkranch.net

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

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