Solved

Problem with xp_cmdshell in a SQL Server Agent Job

Posted on 2011-09-02
6
1,760 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
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.

 
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

Title # Comments Views Activity
encyps queries mssql 15 37
How to manage encyps queries mssql when sending conditioning values 10 37
SQL Query 3 48
SQL Query 2 43
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now