Activation Procs in Service Brokers

Question

Good morning,

I'm seeing some strange behavior while using service brokers for SQL Server 2005.

I've created all the queues and messages etc etc, and have created a procedure to be fired off by my queue once an entry hits it.

This procedure calls xp_cmdshell (I know, but it's a necessary evil).

The strange behaviour is this.  SQL Server is configured to use a user called "[Domain]\SQLServerAgent" the SQL Server agent is also configured with the same user.  When I run the stored procedure (logged in as [Domain]\SQLServerAgent) I can see the brunt of the work happening as this user (performing a profiler trace).... however, when it hits the xp_cmdshell part, for some reason it switches to a completely differerent domain user, and promptly fails with "Execute denied on xp_cmdshell"..

My question is : where would the procedure be deciding to get this new user from?  I would assume the agent, but this seems to not be the case.. 

Regards

Answer 1

See MSDN xp_cmdshell => Remarks:

 xp_cmdshell will be executed under the security context in which the SQL Server service is running

---

Olaf Helper

Blog Xing

Answer 2

Thanks Olaf,

That's what I thought.  In which case, it should be running as the [DOMAIN]\SQLServerAgent. 
I'll try rebooting the server to make sure someone hasnt just changed the service and not restarted.

Regards