SQL Service Broker - The server principal "sa" is not able to access

Question

I am getting a permission error when trying to send a message from my database.

I enamble the proker service:

-- Enable Broker on Initiator
ALTER DATABASE A-DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE A-DB SET ENABLE_BROKER;
ALTER AUTHORIZATION ON DATABASE::A-DB TO [sa];
ALTER DATABASE A-DB SET TRUSTWORTHY ON;
ALTER DATABASE A-DB SET MULTI_USER;
GO

I then create the queues and all that...

When I attempt to send a message, I am seeing the following error in sys.transmission_queue:

An exception occurred while enqueueing a message in the target queue. Error: 916, State: 3. The server principal "sa" is not able to access the database "MYDBNAME" under the current security context.

(Where MYDBNAME is the name of the database we're using)

I THINK the error is caused by this line:

ALTER AUTHORIZATION ON DATABASE::A-DB TO [sa];

That might be a bad thing. But, can I alter the authorisation to put it back to what ever it was? I thought it would be 'dbo' but that's not right. Should it be set to who ever created the database?

Answer 1

Exception - Error: 916, State: 3. The server principal "\" is not able to access the database "" under the current security context.

Error: 916, State: 3. The server principal 'sa' is not able to access the database 'xxx' under the current security context.

Resolution - Execute 'ALTER DATABASE ... SET TRUSTWORTHY ON' command. technet link

Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy.

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON

In my case, both initiator and target service were in the same db, but it was a join to an outer db that caused this error. The Alter should be written for the db in which Svc Broker is enabled.