SQL Server Performance

DBO User Problem

Discussion in 'SQL Server 2005 General DBA Questions' started by amitadmin, Feb 9, 2009.

  1. amitadmin New Member

    Hi Friends,
    I am restoring the Tes1 database by backup of another database Test2 and the "dbo" user is not mapped with any login on Test2 database.
    However, when restoration is completed on Test 1 from Test2 database backup I found the dbo user of Test1 database by default it is mapping with another login e.g. "user1".
    So how can I change the "dbo" login on Test1 database because I have to make similar the user "user1" on Test1 database which would be mapped of "user1" login?

    Note: Before restoration, when I checked the "user1" login mapping specified “dbo” user as a member and I am not able to uncheck the “dbo” user in mapping. Even, I am not able to change and drop the “dbo” user on Test1 database.
    Please provide any method to resolve this.
  2. rohit2900 Member

    Amit,
    Correct if I'm wrong. what I understood from your problem is there is a user named 'user1' in test2 db and you took the backup of it and restored as test1. and after restore you want to change or drop the existing db owner of test1 and change it back to the owner of test2 db.
    FYI: don is not a user it's just a role see... http://msdn.microsoft.com/en-us/library/aa905208(SQL.80).aspx
    Are both these db's on same server or different?
    use sp_changedbowner (http://msdn.microsoft.com/en-us/library/aa259622(SQL.80).aspx) to change the owner of the db or sp_droprolemember (http://doc.ddart.net/mssql/sql70/sp_drop_23.htm) to remove a user from certain role if both the db's are on same server.
    By deafult sa is the owner for all the database unless, you change it or specify it while creating database.
    Hope this helps.
  3. amitadmin New Member

    Thanks for reply, Basically I need to change the "dbo" user login as current login is "user1" but I need to change the "dbo" user login by "sa". I have to make a new "user1" which would be mapped to "user1" login.
  4. amitadmin New Member

    Thanks Rohit, the issue has been resolve after change the database owner.

Share This Page