SQL Service Broker

After trying to receive a message from a service broker queue for a while I found that it will not throw an error if your database isn’t setup correctly. After looking in the table where the messages are stored before they are sent to the queue the solution was easily found

The Error

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.

Where messages are stored before they are sent to queue, and any exceptions which occurred

select * from sys.transmission_queue

Fix

USE [database_name]
ALTER DATABASE [database_name]SET TRUSTWORTHY ON
ALTER DATABASE [database_name] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER AUTHORIZATION ON DATABASE::[database_name] TO [SA];

Developer.com provides a good simple introduction to Service Broker
http://www.developer.com/db/article.php/3640771

Related Posts:

This entry was posted in SQL and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>