October 18, 2021 at 10:03 am
Windows event log filling up with
ERROR 1 : "The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-]' running on queue 'test.dbo.SqlQueryNotificationService-' output the following: '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.'"
And
Error 2: An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. 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.
I am using SQL Server 2017. I have tried changing owner of the database, but still receive error messages. If service broker disabled error stops, but I want to use SQL service broker feature. Is there any other method to solve this error.
October 18, 2021 at 12:29 pm
Have you identified what query specifically it's trying to run and the structure of that query? Use Extended Events to track that down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 18, 2021 at 12:34 pm
What are you trying to do with notification services ?
Chances are you can do it more easy and less intrusive on your server / instance using Extended Events !
With regards to notification services:
- Is your userdatabase enabled for service broker ?
ALTER DATABASE yourdb SET ENABLE_BROKER;
- Is your userdatabase TRUSTWORTHY ?
ALTER DATABASE yourdb SET TRUSTWORTHY ON;
- Did you create the event notification in context of 'sa' ?
We did always perfer to do it that way, so when an account gets removed, the services still work.
/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
EXEC AS LOGIN = 'sa';
go
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION Nsxxx
ON SERVER FOR yourtopic
TO SERVICE 'yourservicename', 'current database';
go
/* Switch back to original user */
REVERT;
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 18, 2021 at 9:58 pm
To add to what Johan said, I'd also check to make sure your database owner is a valid, enabled user. I've had service broker fail to send messages because my database owner was not tied to an appropriate user.
If it worked previously but is failing now, I'd check database owners due to the "dbo" text in the error.
Also, if it worked previously but is failing now, what changed? That change will probably help lead you to why it is failing. If it has been failing for a while and you only noticed now, you may have lost the window to easily capture what changed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 1, 2021 at 5:54 am
What are you trying to do with notification services ?
Chances are you can do it more easy and less intrusive on your server / instance using Extended Events !
With regards to notification services: - Is your userdatabase enabled for service broker ?
ALTER DATABASE yourdb SET ENABLE_BROKER;- Is your userdatabase TRUSTWORTHY ?
ALTER DATABASE yourdb SET TRUSTWORTHY ON;- Did you create the event notification in context of 'sa' ?
We did always perfer to do it that way, so when an account gets removed, the services still work.
/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */EXEC AS LOGIN = 'sa';
go
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION Nsxxx
ON SERVER FOR yourtopic
TO SERVICE 'yourservicename', 'current database';
go
/* Switch back to original user */REVERT;
GO
I have enabled for service broker .
Setting TRUSTWORTHY option to true didn't help. I am still getting the error msg.
November 1, 2021 at 6:20 am
To add to what Johan said, I'd also check to make sure your database owner is a valid, enabled user. I've had service broker fail to send messages because my database owner was not tied to an appropriate user.
If it worked previously but is failing now, I'd check database owners due to the "dbo" text in the error.
Also, if it worked previously but is failing now, what changed? That change will probably help lead you to why it is failing. If it has been failing for a while and you only noticed now, you may have lost the window to easily capture what changed.
It worked previously but is failing now, Error started after Database restore.
November 1, 2021 at 8:31 am
after database restore, you need to double check and setup SSB again !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply