Enabling xp_cmdshell in SQL Server 2005

wanmin444

　　
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.
 
Solution 
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.