Database User dbo is orphaned- How to fix

Question

Hello All

All feedback is welcome

ALTER USER [dbo] WITH LOGIN = [ww]

GO

Msg 15150, Level 16, State 1, Line 14

Cannot alter the user 'dbo'.

Answer 1

Problem

---

Test SQLServer Instance [tst-sqls-03] > Database [Bad] > Database Diagrams > Install Diagram Support > Error Number: 15517 "Cannot execute as the database principal because the principal "dbo" does not exist , this type of principal cannot be impersonated , or you do not have permission.

This error is indeed a token of what happens when there is a mismatch between the database owner according to sys.databases and who is the database owner according to the row for the principal dbo in sys.database_principals.

And the remedy is is to change the database owner to the desired owner:

ALTER AUTHORIZATION ON DATABASE::[Bad] TO DesiredOwner

As for who should own a database, some people in this thread have suggested sa. I think this is bad practice, because this can lead to security issues (if you make other violations of good practice). My preference is that the database owner should be an SQL login that exists soley to own the database in question, and which has been granted no other permissions. But I leave to you to decide.

In any case, there is no other solution to solve your problem than changing the database ownership.

Answer 2

Just change the database owner and the user dbo will be associated with that login.

USE databasename
GO
exec sp_changedbowner 'put login name here'

Answer 3

dbo is not a login, but an alias for the database owner. You can not set the dbo password.

Answer 4

Hi @Stephen Keogh,

Thank you for your reaching out and welcome to Microsoft Q&A!

What you're seeing is perfectly normal. A login that's a member of the sysadmin group does not automatically get a user created in each database, rather they are 'mapped' to the dbo user (database owner)

As Ben says, you just change the database owner and the user dbo will be associated with that login. We can also change the default owner to ‘sa’

It is worth mentioning that “sp_changedbowner 'sa'”, it is deprecated indeed. Do it like this instead:

ALTER AUTHORIZATION ON DATABASE:: TO sa;

Feel free to share your issue here if you have any concerns!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

 

Answer 5

Hi Stephen Keogh,

As per message, it is clear that something is not right with database principal dbo. What actions did you do? Is this database obtained by restoring?

You can refer to this official document, it can help you understand well.

Please notice that this login should be added to the sysadmin role.

In addition, please check it out and show us the results of it returns:

SELECT d.name AS 'Database', s.name AS 'Owner'

FROM sys.databases d

LEFT JOIN sys.server_principals s ON d.owner_sid = s.sid;

Regards,

Lucy Chen