SQL Server 2005: Map dbo to new Login

Question

 

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

Answer 1

 

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

Answer 2

 

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'

Answer 3

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.