0

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

Neo302
  • 150
  • 7

1 Answers1

0

I don't remember off the top of my head exactly how it goes, but the execution context of activated procedures is odd. Set up a trace (or extended events) and see who's calling your procedure. My guess is that you'll be surprised. You can either throw an "execute as" clause on the procedure, get into some module signing, or grant permissions to the procedure executor.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thanks. I ran a trace and still can't see what the issue is. In the activation SP, I can create a temp table as in 'select 1 as c1 into #a', but I can't do anything with objects that exist in the DB (tempdb.dbo.Table1). – Neo302 Nov 30 '12 at 20:30
  • Yes, I understand. In the trace, what login is the execution context for your activation sproc? That login will need permissions (by whatever mechanism) to your objects in tempdb. – Ben Thul Dec 01 '12 at 00:02
  • What might be the reason to have service broker enabled in tempdb? I am a new sql server administrator and I am wondering if I can safely disabled it. And is it possible to find out when was it enabled? – Bartosz Siemasz Oct 07 '22 at 10:37
  • @BartoszSiemasz - that's really a topic for a new question. I'd suggest posting it on dba.stackexchange. If you create it and link to it here, I'll be happy to weigh in. – Ben Thul Oct 07 '22 at 14:17