?
Solved

Problem with xp_cmdshell in a SQL Server Agent Job

Posted on 2011-09-02
6
Medium Priority
?
1,882 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 57

Assisted Solution

by:HainKurt
HainKurt earned 400 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 30

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 6

Expert Comment

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

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 30

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 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