Wednesday 15 July 2015

How to Enable ‘xp_cmdshell’ for SQL Server?

Version : Valid for all SQL server 2012, 2014, 2016

Message: 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', search for 'xp_cmdshell' in SQL Server Books Online.

General Questions related with xp_cmdshell :
1) What is the usage of xp_cmdshell and
2) How to execute BAT file using T-SQL?

Basic Answers: Answer is xp_cmdshell can execute shell/system command, which includes batch file.

Information:  xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
By default, the xp_cmdshell option is disabled. To enable xp_cmdshell option use the following code.

SQL server syntax to enable xp_cmdshell:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- To update the currently configured value for this feature.

Example 1: To list all text file in a folder from query editor
exec master.dbo.xp_cmdshell 'dir E:\work\*.txt'
Volume in drive E has no label.
Volume Serial Number is 3E5F-30C6
Directory of E:\work
06/29/2015  11:27 AM                 0 8001.txt
1 File(s)              0 bytes
0 Dir(s)  462,489,137,152 bytes free

Example 2: To create a folder using Sql server query editor
exec master.dbo.xp_cmdshell 'mkdir E:\work\sql_sp_cmdshell\'
Output : NULL
Message: (1 row(s) affected)

Hope you enjoyed lot with given SQL server tricks :) Stay tune