Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Enabling xp_cmdshell in SQL Server 2005

By:   |   Read Comments (4)   |   Related Tips: More > Security

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.

Sometimes there may be a need to run some external processing from within SQL Server.  So to make this task easy for both DBAs and developers Microsoft has a built-in extended stored procedure called xp_cmdshell.  With this extended stored procedure you have the ability to run any command line process, so you can embed this within your stored procedures, jobs or batch processing.  In SQL Server 2000 this option is enabled by default, but to limit access to using xp_cmdshell only members of the sysadmin server role have default rights. To extend rights to other users you can use the GRANT statement to allow execute rights.

With SQL Server 2005 the use of xp_cmdshell is turned off by default. As SQL Server continues to mature Microsoft has put many safeguards in place to minimize the security risks of unwanted code being executed within SQL Server or outside of SQL Server. 

If you don't enable xp_cmdshell and you issue a command such as the following to get a directory listing of the C: drive:

xp_cmdshell 'dir c:\'

you get the following error message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

So in order to use xp_cmdshell whether you are a sysadmin or a regular user you need to first enable the use of xp_cmdshell.  Doing this is similar to what was done when enabling the dedicated administrator connection.  This can be done by using sp_configure:

  • sp_configure 'xp_cmdshell', '1'

or by using the SQL Server Surface Area Configuration as follows:

  • launch the SQL Server Surface Area Configuration.tool which can be found under SQL Server 2005 Configuration Tools in the SQL Server 2005 menus
  • select "Surface Configuration Area for Features".
  • check the box for "xp_cmdshell"
  • select "Apply"

Next Steps

  • Check whether or not you are using xp_cmdshell for previous versions of SQL Server and enable it if it is still needed when you make the upgrade to SQL 2005
  • With a new installation of SQL Server 2005 check to see if you really need this option enabled.  From a security perspective it is best to minimize any risks, so keep this option disabled makes the most sense unless there is a real need.

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Thursday, July 21, 2016 - 4:43:13 AM - Nandakumar Back To Top

I do not know how to  enable the xp_cmdshell in SQL Server 2005 . and then i did see your website and  i enabled it in my client database.

thanks a lot . No words to say .



Monday, September 30, 2013 - 4:19:47 PM - Adelson Back To Top

Thanks !!!!

Tuesday, September 24, 2013 - 4:26:47 AM - stroct1 Back To Top

Thanks for helping

Wednesday, November 14, 2012 - 3:37:37 AM - narasimha Back To Top

thanks a lot , it is very helpful to us keep it uptopdate more and more 


best regards,


Learn more about SQL Server tools