Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

execute as user 'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much