Cannot find xp_cmdshell in master database

Posted on 2009-02-10
Medium Priority
Last Modified: 2012-05-06

I only saw resources describing how to enable xp_cmdshell in SQL 2005. I want to know how to do it for SQL 2008. And I am not having the sysadmin rights. So by default I think I cannot run xp_cmdshell command. How can system admin create an account that can run xp_cmdshell command?
Question by:hanhn
  • 3
  • 2
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23598653
Basically, you have to enable xp_cmdshell option in Surface Area Configuration manager. Otherwise nobody will be able to use it.

Once you enable it, It is by default available only to members of sysadmin privileges only. If any other users want to run it they need to have a proxy account enabled (Below is referred from MSDN).

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

Author Comment

ID: 23598664
But where is the Surface Area Configuration Manager on SQL 2008?
LVL 57

Accepted Solution

Raja Jegan R earned 1000 total points
ID: 23598684
If you are using SQL Server 2008 Express edition, you may not have it.

Otherwise Go to Program Files --> SQL Server 2008 --> Configuration Tools --> SQL Server Surface Area Configuration.

Open it and click Surface Area Configuration for Features and you will see xp_cmdshell options. Check it to enable it.

Or you can follow the alternate approach to enable it.

EXECUTE sp_configure 'show advanced options', 1
EXECUTE sp_configure 'xp_cmdshell', '1'
EXECUTE sp_configure 'show advanced options', 0

this will help you to enable xp_cmdshell
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 23602562
>> How can system admin create an account that can run xp_cmdshell command? <<

A sysadmin would have to enable xp_cmdshell AND set up a proxy account that would run it for you.  [Or GRANT you CONTROL SERVER permission, which is probably not likely :-) .]

Author Comment

ID: 23607483
Just wondering something. Yes you are right about the proxy account. But now it raises me some question. My xp_cmdshell has to be within the trigger .. My intention is to call my program from trigger while a row is inserted to the table.

So if the row were to insert from the VB program, is my program has to use that proxy account for data connection? Only then xp_cmdshell within the trigger will be able to execute or? I am not very clear about this part. Can clarify how it works?
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23608451
You can try one another option too if you are obtaining any errors while executing the xp_cmdshell command.

Try using the sp_xp_cmdshell_proxy_account command through which you can associate it with an account in that server machine. If that windows account holds permission for that folder, you can write it.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

839 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