none
SQL Server 2005: Map dbo to new Login

    Pregunta

  •  

    Hi all!

    1. I have a database called "MyDB".
    2. On that database I have "sa" login mapped to "dbo" user.
    3. I have another login for the MyDB database called "Philip" which is mapped to user "Philip"

    Note: default schema = dbo for all

     

    How do I make Login "Philip" map to User "dbo"?

     

    I have tried

     

    Exec sp_changedbowner "Philip"

     

    And recvd the following error:

     

    Msg 15110, Level 16, State 1, Line 1

    The proposed new database owner is already a user or aliased in the database.

     

     

    Any help would be greatly apreciated!

    Thanks!

     

    Philip
    lunes, 20 de octubre de 2008 21:17

Respuestas

  •  

    As the error message suggests, the operation failed because there is already a user (sys.database_principals) with the matching SID for login “Phillip”.

    SELECT * FROM sys.database_principals WHERE sid = suser_sid('sa')

    go

     

      You have to either choose a different login to become the DBO, remap the user to a different login using ALTER USER DDL before changing the DB ownership.

      BTW. The preferred syntax to change the DBO is ALTER AUTHORIZATION.

     

      I hope this information helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    martes, 21 de octubre de 2008 3:04
    Moderador

Todas las respuestas

  •  

    As the error message suggests, the operation failed because there is already a user (sys.database_principals) with the matching SID for login “Phillip”.

    SELECT * FROM sys.database_principals WHERE sid = suser_sid('sa')

    go

     

      You have to either choose a different login to become the DBO, remap the user to a different login using ALTER USER DDL before changing the DB ownership.

      BTW. The preferred syntax to change the DBO is ALTER AUTHORIZATION.

     

      I hope this information helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    martes, 21 de octubre de 2008 3:04
    Moderador
  •  

    Tried

     

    USE MyDB

    ALTER USER dbo WITH LOGIN = Philip;

    GO

     

    but recvd the following error:

     

    Msg 15150, Level 16, State 1, Line 2
    Cannot alter the user 'dbo'

    martes, 21 de octubre de 2008 17:39
  • dbo is a special user and can not be altered. What Raul was referring to was either:

    alter user phillip to login = foo; and then alter the database owner to be login phillip using the alter authorization statement.

    or

    use alter authorization to designate a different login other than phillip as the database owner.

     

    miércoles, 29 de octubre de 2008 20:50
    Moderador