Solved

Problem with xp_cmdshell in a SQL Server Agent Job

Posted on 2011-09-02
6
1,794 Views
Last Modified: 2012-08-14
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
Comment
Question by:Feridun Kadir
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:markterry
ID: 36475001
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
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 100 total points
ID: 36475004
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
 
LVL 29

Author Comment

by:Feridun Kadir
ID: 36475179
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 6

Expert Comment

by:markterry
ID: 36475416
Run the job with that user...
0
 
LVL 29

Accepted Solution

by:
Feridun Kadir earned 0 total points
ID: 36492119
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
 
LVL 29

Author Closing Comment

by:Feridun Kadir
ID: 36518446
HainKurt provided information that I already knew, but it was helpful to have it confirmed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question