Service Broker Activation SP and TempDB Object Permissions

Question

Hello, 

I have an activation SP that calls another SP that calls other SPs to insert and update a table that exists in TempDB. I've created the queue with a user with SA permissions and the activation SP has SA permissions (to try and get around the issue).

I am continuing to get this error: The SELECT permission was denied on the object 'xxxxxxxx', database 'tempdb', schema 'dbo'.

I have the ability to execute the process without Service Broker (calls the SPs Service Broker is calling, just directly) and it works fine.

Anyone have any ideas?

Thank you

Answer 1

You might be running into this issue possibly due to cross database ownership chaining not set up correctly. Please follow this link for more details - http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 2

Hello, 

The user has SA permissions. I checked out the article and looked at the db and tempDB has it turned on already. I also gave the user full permissions on tempdb and ran a trace and everything appears to be running with the user. 

What is interesting is in the activation SP, I can do 'select 1 as c1 into #a', but I just can't do anything with objects that already exist in tempdb (tempdb.dbo.table1)

Thanks

Answer 3

hmmm.....on your CREATE QUEUE statement for the Queue which the Activation Proc is on, have you specified for the procedure to EXECUTE AS OWNER ? What do you have under EXECUTE AS in your CREATE QUEUE statement ?

- http://msdn.microsoft.com/en-us/library/ms190495.aspx

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 4

Security on Service broker is quite complicated with a mirad of options.
Even though everything specifies SA, it won't help because the security for background jobs is limited to the scope of the database.

A quick way to solve this is to make the source database Trustworthy. This will allow the activation proc to 'break out' of its database.

ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON

You should look up the command in BOL or online to understand the security implications, but unless you have multiple databases on the server which must not be able to interact, then this is probably adequate.

I usually make the database owner sa and then use "AS dbo" on all service broker components.

For a more secure solution, you have to resort to certificates.

Of course, you could just put the tables in the same database instead of tempdb.