I have an SQL script that includes an xp_cmdshell statement. If I run the script in Management Studio whilst logged in as a particular user everything works.
The user (let's call him userA) is a member of sysadmin.
If I include the script in a job the job fails. SQL Server agent runs under a domain service account and I'm using Run as user UserA because the script needs to query a database that UserA has access and the domain service account does not.
The error is:
Msg 229, Sev 14, State 5, Line 1 : The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000]
The server is SQL Server 2008 R2. xp_cmdshell is enabled. I am not using a proxy (userA is a member of sysadmin role)
Any guidance would be appreciated.