asked on

Problem with xp_cmdshell in a SQL Server Agent Job

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.

markterry

Add that domain user to the SQL Server logins, or set the job to execute using UserA. if you are using SQL Jobs Agent, this is a section in there. If you are using sqlcmd and task manager, then you need to include it as a parameter.

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Feridun Kadir

ASKER

Thanks for your posts. UserA is windows login and has been added to SQL Server and made a member of the sysadmin role so it should run under the context of the SQL Server service.

markterry

Run the job with that user...

ASKER CERTIFIED SOLUTION

Feridun Kadir

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Feridun Kadir

ASKER

HainKurt provided information that I already knew, but it was helpful to have it confirmed.