• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1994
  • Last Modified:

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.
0
Feridun Kadir
Asked:
Feridun Kadir
  • 3
  • 2
2 Solutions
 
markterryCommented:
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.
0
 
HainKurtSr. System AnalystCommented:
does this apply to you?:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running.

When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail.

This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server

have a look at this forum:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45799
0
 
Feridun KadirPrincipal ConsultantAuthor Commented:
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.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
markterryCommented:
Run the job with that user...
0
 
Feridun KadirPrincipal ConsultantAuthor Commented:
I had to treat UserA as if he wasn't a member of sysadmins and grant him access to xp_cmdshell explicitly. I wonder whether there is a bug or a change in SQL 2008 R2 because this was a not a problem for me in SQL 2005.
0
 
Feridun KadirPrincipal ConsultantAuthor Commented:
HainKurt provided information that I already knew, but it was helpful to have it confirmed.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now