I've been attempting to learn how to set up Service Broker through Microsoft's "Completing a Conversation in a Single Database" tutorial. However, when I actually try to send a message, it fails until I change the owner of the database from my account (which appears to be the default) to SA. What's going on here?
I'm using the AdventureWorks2008R2 datafile from CodePlex attached like this:
CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE\MSSQL\DATA\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG;
I can enable Service Broker and create the message types, contract, and target/initiator services and queues, as instructed in the tutorial:
-- Creating the Conversation Objects -- http://technet.microsoft.com/en-us/library/bb839488(v=sql.105).aspx USE master; GO ALTER DATABASE AdventureWorks2008R2 SET ENABLE_BROKER; GO USE AdventureWorks2008R2; GO CREATE MESSAGE TYPE [//AWDB/1DBSample/RequestMessage] VALIDATION = WELL_FORMED_XML; CREATE MESSAGE TYPE [//AWDB/1DBSample/ReplyMessage] VALIDATION = WELL_FORMED_XML; GO CREATE CONTRACT [//AWDB/1DBSample/SampleContract] ([//AWDB/1DBSample/RequestMessage] SENT BY INITIATOR, [//AWDB/1DBSample/ReplyMessage] SENT BY TARGET ); GO CREATE QUEUE TargetQueue1DB; CREATE SERVICE [//AWDB/1DBSample/TargetService] ON QUEUE TargetQueue1DB ([//AWDB/1DBSample/SampleContract]); GO CREATE QUEUE InitiatorQueue1DB; CREATE SERVICE [//AWDB/1DBSample/InitiatorService] ON QUEUE InitiatorQueue1DB; GO
...and I can begin the conversation:
-- Beginning a Conversation and Transmitting Messages -- http://technet.microsoft.com/en-us/library/bb839499(v=sql.105).aspx DECLARE @InitDlgHandle UNIQUEIDENTIFIER; DECLARE @RequestMsg NVARCHAR(100); BEGIN TRANSACTION; BEGIN DIALOG @InitDlgHandle FROM SERVICE [//AWDB/1DBSample/InitiatorService] TO SERVICE N'//AWDB/1DBSample/TargetService' ON CONTRACT [//AWDB/1DBSample/SampleContract] WITH ENCRYPTION = OFF; SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>'; SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//AWDB/1DBSample/RequestMessage] (@RequestMsg); SELECT @RequestMsg AS SentRequestMsg; COMMIT TRANSACTION; GO
However, at this point, if I try to receive the conversation, I don't see anything. This query (still following the tutorial) returns
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER; DECLARE @RecvReqMsg NVARCHAR(100); DECLARE @RecvReqMsgName sysname; BEGIN TRANSACTION; WAITFOR ( RECEIVE TOP(1) @RecvReqDlgHandle = conversation_handle, @RecvReqMsg = message_body, @RecvReqMsgName = message_type_name FROM TargetQueue1DB ), TIMEOUT 1000; SELECT @RecvReqMsg AS ReceivedRequestMsg;
The transmission queue ('select * from sys.transmission_queue') shows one record that is not being transmitted. In the transmission_status field I see:
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.
How is this possible? Certainly I must have permission over objects in my own server, on which I am sysadmin. And I can't imagine "dbo" doesn't exist - this is the default AdventureWorks database straight from Microsoft, and the dbo schema is right there in the schemas list.
Following the unexplained advice on a blog I found, I changed the database owner to SA:
ALTER AUTHORIZATION ON DATABASE::[dbname] TO [SA]
...and the message transmitted perfectly.
So, my question is, what did I really change here? Why does SA, who should have the same rights as me as sysadmin, suddenly make a difference? I presume I'm misunderstanding something about SQL Server's security.