How to resolve MS SQL Error for Event ID 119 and 28005 ?

Question

How to resolve MS SQL Error for Event ID 119 and 28005 ?

Louis Leung 0

We have 2 MS SQL 2014 Servers running on Windows Server 2016. We are running mirror mode for our application database. However we found frequently we got the following errors:

Event Id : 119

Report Server Windows Service has not been granted access to the catalog content

Event Id: 28005

An exception occurred while enqueueing a message in the target queue. Error 15517, State 1, Cannot execute as database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission

Although those errors seem not affect our application but they appeared evey day and fill up our log.

Please advice how to resolve it

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
LiHongMSFT-4306 31,566 Reputation points

2023-08-04T08:33:35.4433333+00:00

Hi @Louis Leung

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

1 answer

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
LiHongMSFT-4306 31,566 Reputation points

2023-08-04T08:33:35.4433333+00:00

Hi @Louis Leung

Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 1

LiHongMSFT-4306 31,566

Hi @Louis Leung

Event Id : 119 Report Server Windows Service has not been granted access to the catalog content

Please refer to this thread: Report Server Windows Service has not been granted access to the catalog content.

Event Id: 28005 An exception occurred while enqueueing a message in the target queue. Error 15517, State 1, Cannot execute as database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo. It's as simple as:

ALTER AUTHORIZATION ON DATABASE::Example TO sa;

Please refer to this blog for more details: Troubleshooting SQL Server Error 15517.

Best regards,

Cosmog Hong

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.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-06T09:25:23.06+00:00

In addition to what Cosmog says: this often happens when you restore a database from one server to another. When you restore a database, and a database with that name does not exist, you will become the owner of the database. If the database already exists on the server, the owner of the database in sys.databases will be retained. But the owner is also recorded in the database, in the sid column in sys.database_principals for uid = 1, that is, dbo. However, this value is never updated when you restore the database, so you get a mismatch. And this mismatch leads to this error when there is impersonation involved.

The correct solution is exactly what Cosmog says: change the owner of the database. (Although in my opinion, having sa as the owner is not best practice, but each database should be owned by an SQL login that exists only to own that database.)

It's a very good habit to always set the owner explicitly after having restored a database.

Share via

How to resolve MS SQL Error for Event ID 119 and 28005 ?

1 answer

Your answer