Wednesday, September 19, 2012 10:26 AM
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..
Wednesday, September 19, 2012 10:38 AM
See MSDN xp_cmdshell => Remarks:
xp_cmdshell will be executed under the security context in which the SQL Server service is running
Olaf HelperBlog Xing
Wednesday, September 19, 2012 11:12 AM
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.