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'.

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.

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'

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

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. <a href="https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fanswers%2Fsupport%2Femail-notifications&data=05%7C02%7Cv-xinc%40microsoft.com%7C9b8c827cb42642ce852408dc43e66a2a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C638459904116612195%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=Enb7k6yaPROUCn4EQgXIrCLX2qXwUpzmasNuBLy%2BznA%3D&reserved=0" rel="ugc nofollow">https://docs.microsoft.com/en-us/answers/support/email-notifications

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

Database User dbo is orphaned- How to fix

Stephen Keogh 40

Hello All

All feedback is welcome

ALTER USER [dbo] WITH LOGIN = [ww]

Msg 15150, Level 16, State 1, Line 14

Cannot alter the user 'dbo'.

Stephen Keogh 40 Reputation points

2024-07-18T14:39:48.5666667+00:00

SELECT @@version

USE [IntegratorTest]

GO

SELECT SUSER_SNAME(owner_sid)

FROM sys.databases

WHERE name = 'IntegratorTest'

GO

SP_HELPUSER dbo

GO

ALTER USER [dbo] WITH LOGIN = [ww]

GO
Stephen Keogh 40 Reputation points

2024-07-18T14:41:18.6633333+00:00
Microsoft SQL Server 2022 (RTM-CU13-GDR) (KB5040939) - 16.0.4131.2 (X64)

Jun 21 2024 21:57:17 Copyright (C) 2022 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

(1 row affected)

????????\ric

(1 row affected)

UserName,RoleName,LoginName,DefDBName,DefSchemaName,UserID,SID

dbo,db_owner,NULL,NULL,dbo,1 ,0x010500000000000515000000CF1E7D56A162A546F44C5A13F5190000

Msg 15150, Level 16, State 1, Line 14

Cannot alter the user 'dbo'.

Completion time: 2024-07-18T15:39:59.9702049+01:00
LucyChenMSFT-4874 4,090 Reputation points

2024-07-22T06:21:23.9833333+00:00

Hi @Stephen Keogh,

We haven't heard any response from you yet.

Is this issue solved now? If you have any questions, please feel free to share with us.

May I humbly suggest that you accept Answer if they helped. It may help others who see the thread in seek of help.

Your action would be helpful to other users who encounter the same issue and read this thread. :)

Thanks for your understanding. Wish you have a nice day!

Best regards,

Lucy Chen

Accepted answer

Erland Sommarskog 109.4K Reputation points MVP

2024-07-22T15:48:17.3766667+00:00
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.
Please sign in to rate this answer.

1 person found this answer helpful.
LucyChenMSFT-4874 4,090 Reputation points

2024-07-23T02:30:52.9533333+00:00

Hi Stephen Keogh,

As I said in my latest reply, I also agree with Erland!

May I humbly suggest that you accept Answer if they helped. It may help others who see the thread in seek of help.

Your action would be helpful to other users who encounter the same issue and read this thread. :)

Thanks for your understanding. Wish you have a nice day!

Best regards,

Lucy Chen
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

4 additional answers

Ben Miller-(DBADuck) 105 Reputation points MVP

2024-07-18T19:23:10.5833333+00:00
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'
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Bruce (SqlWork.com) 63,421 Reputation points

2024-07-18T15:26:35.5166667+00:00

dbo is not a login, but an alias for the database owner. You can not set the dbo password.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
LucyChenMSFT-4874 4,090 Reputation points

2024-07-19T02:35:49.4233333+00:00

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
Please sign in to rate this answer.
Stephen Keogh 40 Reputation points

2024-07-19T06:48:50.6033333+00:00

Thank you for the feedback

I wish to map User [dbo] to an existing Login [ww] who is not the database Owner and should not be

Regards

Steve

LucyChenMSFT-4874 4,090 Reputation points

2024-07-19T07:05:05.4766667+00:00

Hi @Stephen Keogh,

Thank you for your kindly feedback!

Maybe what I said in the answer is a little vague.

The owner of the database is the only login that can be "mapped to dbo", but all sysadmins, including sa, always connect to the database as dbo. All users who are in the sysadmin role maps to the user dbo in every database.

Please refer to this link, we can get the information below:

Feel free to share your issue here if you have any concerns, we'll offer you more support.

Regards,

Lucy Chen

Erland Sommarskog 109.4K Reputation points MVP

2024-07-19T07:54:58.4833333+00:00

I wish to map User [dbo] to an existing Login [ww] who is not the database Owner and should not be

You cannot do that. The database user dbo always maps to the login who is the database owner. There is one exception: you have restored the database from another server. And this latter situation is an anomaly that can cause problems in some situations.

Ben Miller-(DBADuck) 105 Reputation points MVP

2024-07-19T12:36:57.13+00:00

With what you said about [ww] not being the database owner, I am curious about the problem you are trying to solve? Mapping a user to dbo is making the user a database owner, adding the user to db_owner role is making the user a database owner. So if [ww] should not be the database owner but you want it to be mapped to [dbo], then you are not going to be able to solve the problem of [ww] being mapped to dbo without being a database owner.

Erland Sommarskog 109.4K Reputation points MVP

2024-07-19T20:42:19.0266667+00:00

adding the user to db_owner role is making the user a database owner.

Nah, that gives the user the same powers that the database owner has, but the login the user maps to is not the owner of the database.

But, yes, it is a good question what underlying problem that Stephen is trying to solve.

Ben Miller-(DBADuck) 105 Reputation points MVP

2024-07-19T21:21:06.33+00:00

Sorry Erland. I meant that they have the same permissions, not that it is a database owner. ;-0

Stephen Keogh 40 Reputation points

2024-07-22T07:53:34.62+00:00

Hello All

Thank you for all the feedback. The technical assistance I receive on here is better that the paid support we get on another big time database product

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.

(Microsoft SQL Server, Error: 15517)"

Bad Database (Diagram does not work)

SSMS > [Bad] > Security > Users > [dbo] right-click > User name [dbo] Login name []

SSMS > [Bad] > Files > Owner xxxxxxxx\ric

Good Database (Diagram does work)

SSMS > [Good] > Security > Users > dbo [dbo] right-click > User name [dbo] Login name [ww] = disabled SQLServer Login

SSMS > [Good] > Files > Owner = xxxxxxxx\ric

Thanks All

Steve

LucyChenMSFT-4874 4,090 Reputation points

2024-07-22T08:34:57.9666667+00:00

Hi @Stephen Keogh,

Thank you for your affirmation of the forum!

Could you please tell us if the issue you raised in this thread has been resolved? Please don't forget to accept the answer if it helped. Your action would be helpful to other users who encounter the same issue and read this thread. :)

According to the community regulations, if you meet a new issue, you'd better open a new thread!

Thanks for your understanding! Wish you have a nice day!

Regards,

Lucy Chen

Stephen Keogh 40 Reputation points

2024-07-22T08:40:15.09+00:00

Hi Lucy

My problem has not been solved

In my latest posting I have clarified why this thread was opened as "User [dbo] is orphaned - how to fix"

Thanks

Stee

LucyChenMSFT-4874 4,090 Reputation points

2024-07-22T08:58:08.7933333+00:00

Hi Stephen Keogh,

Thank you for your kindly feedback!

Who's your database owner? We recommend you select user ‘sa’ or NT AUTHORITY\SYSTEM as this database's owner.

The owner of the database is the only login that can be "mapped to dbo", but all sysadmins, including sa, always connect to the database as dbo. All users who are in the sysadmin role maps to the user dbo in every database.

Please refer to this thread.

Stephen Keogh 40 Reputation points

2024-07-22T09:21:36.43+00:00

Hi Lucy

Who's your database owner?<<

See above

SSMS > [Bad] > Files > Owner xxxxxxxx\ric

SSMS > [Good] > Files > Owner = xxxxxxxx\ric

Regards

Steve
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
LucyChenMSFT-4874 4,090 Reputation points

2024-07-22T09:40:13.59+00:00

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
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Database User dbo is orphaned- How to fix

4 additional answers

Your answer