none
newbie question: change login name of dbo user

    Question

  • My database has a user "dbo" which is dbo of all database objects. The login name for this user is e.g. "Login1". Now I need to change the login name for user "dbo" from "Login1" to a new login name "Login2".

    It is not possible to change the related login name of the user "dbo" in the user's properties dialog. And it is also not possible to change or remove the dbo relation in the login name's properties dialog (because a dbo user can not be removed).

    Is there any possibility to change the login name of an dbo user? How can I do it?

    Thursday, June 22, 2006 9:52 AM

Answers

  •  sp_changedbowner

    Changes the owner of the current database.

     

    Examples

    This example makes the user Albert the owner of the current database and maps existing aliases to the old database owner to Albert.

    EXEC sp_changedbowner 'Albert'
    in your case that would be
    EXEC sp_changedbowner 'login2'
     
     
    Thursday, June 22, 2006 12:38 PM

All replies

  •  sp_changedbowner

    Changes the owner of the current database.

     

    Examples

    This example makes the user Albert the owner of the current database and maps existing aliases to the old database owner to Albert.

    EXEC sp_changedbowner 'Albert'
    in your case that would be
    EXEC sp_changedbowner 'login2'
     
     
    Thursday, June 22, 2006 12:38 PM
  • additional information for user DBO
    

    Database Owner (dbo)

    The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

    For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.

    The dbo user cannot be deleted and is always present in every database.

    Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

    • Belong to the user creating the object, not dbo.

    • Are qualified with the name of the user who created the object.

     

    Thursday, June 22, 2006 1:11 PM
  • If you look closely the owne of the database where the dbo user is linked to Login 1 will be login1.

    so to change the dbo user to map to login2 change the onwer of the database to the login2. This way it will change the dbo user mapping to login2.

    You canc change th eowner of the database from the SSMS bu going to the properties of the database->go to files option->change onwer.

    or use the sp_changedbowner command.

    • Proposed as answer by Amvin [MSFT] Tuesday, September 21, 2010 10:08 PM
    Tuesday, September 21, 2010 10:06 PM
  • What AMVIN said worked for me... when i made the database it took my domain username as an owner and as i'm not eternal here i didn't want to let it that way... had troubles with SP_changeowner and alter user... never tested the "file" owner.
    Monday, March 12, 2018 12:14 PM
  • This is an old thread. In modern SQL Server versions, one should use ALTER AUTHORIZATION to change the database owner:

    ALTER AUTHORIZATION ON DATABASE::YourDatabase TO YourNewOwner;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, March 12, 2018 12:32 PM
    Moderator