August 10, 2020 at 10:14 am
Windows event log filling up with event id - 9724
ERROR: "The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-]' running on queue 'database.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.'"
I am using Sql server 2008 R2. I have tried changing owner of the database still error message comes.The service broker is disabled in the database except tempdb and msdb.
How to solve this error?
August 10, 2020 at 9:16 pm
If Service Broker is disabled, does anyone care about the conversations? If memory serves, you can do ALTER DATABASE db SET NEW_BROKER and it will clear up everything. And I mean everything! So make sure that no one cares about Service Broker before you do this!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 14, 2020 at 10:18 am
Broker is disabled in the database which is mentioned in error.but in other 2 database the service broker option is enabled.
Can this query be used to delete the queue.
select * from sys.service_queues
DROP QUEUE queueName
I don't know whether is it in use.
How to check if service broker is used? Is t possible to check where the SqlQueryNotificationService is used by its id in the log?
August 14, 2020 at 1:49 pm
I don't think dropping the queue helps. Rather it makes things worse.
But the best start may be to ask around. There must be someone who knows what this database is supposed to do. Is this a production database? Or has it been restored from prod to another environment?
Query Notification could be a source of the message, but I would not expect there to be a activation procedure in that case.
If you don't want to set NEW_BROKER, you can look in sys.conversation_endpoints and to END CONVERSATION WITH CLEANUP on all conversation handles. Again, if you do that blindly, you may mess up other users of Service Broker in the database.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 12, 2020 at 7:43 am
The database is restored from Production database to the testing server.Is it possible to get information from where it is calling.
September 12, 2020 at 8:43 am
I guess then that Service Broker is actually used in production. And the message comes from the production environment, but cannot be processed because of this error.
Did you change the owner when you restored the database? The error message indicates that the owner recorded in sys.database_principals for dbo is not the same as the owner according to sys.databases.owner_sid.
Change the owner of the database (so it is set to the same value in both places), and this should resolve the problem, I expect.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply