"xp_cmdshell" is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code. If needed the output of these commands will be returned to the calling routine.

Now not just anyone can run this extended stored procedure. If you want to execute this extended stored procedure, you will either need to be a member of the sysadmin role, or have the xp_sqlagent_proxy_account set up on your SQL Server. If a login executing this extended stored procedure is a member of the sysadmin role then the submitted command will run under the security context associated with the SQL Server Service account in which it runs. If the login executing this procedure is not a member of the sysadmin role, then the command uses the xp_sqlagent_proxy_account login security context for determining whether operating system commands can and cannot be run. If there is no xp_sqlagent_proxy_account then using this procedure will fail for all users not in the sysadmin role.

On the surface being able to run this extended stored procedure does not seem like much, but if the MSSQLSERVER service account has local administration rights then you can use this extended stored procedure to perform any windows operating system command. Therefore, under this circumstance the xp_cmdshell can create quite a security hole. Saying that, you need to be careful how you set up your environment to make sure someone does not corrupt your system by using the almighty powerful xp_cmdshell extended stored procedure. So let's look at different examples of ways to use xp_cmdshell.

Let's start out with a simple directory search example. Say you do not have access to the physical SQL Server machine, but you want to see all the files in the "C:\temp" directory that have a ".sql" extension. You can do this by issuing the following command:

exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'

Now that wasn't too exciting, so let's come up with a more useful example. Imagine you are in the process of building a new SQL Agent job using Enterprise Manager. To organize the output from all the steps in this new job, you want the output of each step to go into a common directory on the server, where each step has its own output file. The only problem is the new directory does not exist. Assume you do not have access to the server, or do not want to walk down the hall to the computer room to create this new directory. No problem you can just enter the following command in Query Analyzer from the privacy of your office to accomplish creating the new directory:

exec master.dbo.xp_cmdshell 'mkdir "c:\temp\SQL Agent Output\new_job\"'

As you can see, it is very easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run executables? The answer is YES! Suppose you have a DTS Package that you want to run via a stored procedure. There is no "sp_executeDTS" stored procedure, but you can use the following xp_cmdshell command to run your DTS package:

exec master.dbo.xp_cmdshell 'dtsrun -E -Sserver1 -N"Export Invoices"'

This command executed the "dtsrun" executable with the necessary parameters to run the "Export Invoices" DTS package. Are you starting to see how the xp_cmdshell extented store procedure can provide you with some alternatives and also expands the functionality you can build into a T-SQL script.

For my last example, suppose you have a T-SQL script stored on a hard drive that you would like to execute via a number of different stored procedures. This T-SQL script does a number of T-SQL commands and you what those same commands to be executed in many different stored procedures. In addition, the T-SQL script is generated by some other organization on a daily basis, so you are unable to include the lines of code in each stored procedure, without changing the stored procedures daily. Therefore, you want to use xp_cmdshell to execute the T-SQL script. To perform this execution you perform the following command in each of your stored procedures:

exec master.dbo.xp_cmdshell 'osql -E -Sserver1 -i c:\temp\nightly.sql'

Here I have used xp_cmdshell to execute the SQL Server osql utility to process the T-SQL commands contained in the script file "c:\temp\nightly.sql". See how easy it is to execute a T-SQL batch script via a stored procedure using the xp_cmdshell extended stored procedure!

If you use xp_cmdshell in a script, you need to be aware that if the operating system command fails, your script will not detected that the error occurred. If you want to perform something differently if the operating system command fails then you will need to code your xp_cmdshell execution so you can capture the error. Let me demonstrate what I am talking about.

Say you want to copy an operating system file before you perform some work, but if the operating system file you want to copy does not exist to copy you do not want to perform the additional work. If you execute xp_cmdshell like so:

exec master.dbo.xp_cmdshell 'copy c:\temp\doesnotexist.txt c:\temp\workfile.txt'
Print 'Copy worked now we can do some more stuff.'
...

The "Print" statement above will be executed regardless of whether the file to be copied exists or not. Therefore, if you need to control your logic based on whether or not the "copy" command actually worked, then you should code your xp_command shell execution like this:

DECLARE @rc int
EXEC @rc = master.dbo.xp_cmdshell 'copy c:\temp\doesnotexist.txt c:\temp\workfile.txt'
print @rc
IF @rc <> 0
BEGIN
  PRINT 'Copy Failure Skip work'
END
ELSE
BEGIN
  Print 'Copy worked now we can do some more stuff'
  ....
END

Here I captured the return code of the execution of xp_cmdshell in a variable @rc. This allows me to then check the value held by variable @rc and then I programmatically determine whether or not some additional code should be run or not.

When you use the extended stored procedure xp_cmdshell it runs commands in the background. Because of this, xp_cmdshell "MUST NOT" be used to run programs that require user input. If you try to execute a program that requires user input, the xp_cmdshell process will hang. The process hangs because the program is waiting for user input, but xp_cmdshell will never display the user interface to allow the user to enter data.

If you write lots of T-SQL scripts to automate your Database Administration task then you probably already know about the extended stored procedure xp_cmdshell. This extended stored procedure allows you additional flexibility to get at operation system resources via T-SQL, which is extremely valuable for managing your SQL Server environment. If you have not yet experimented with the power of xp_cmdshell, then take a few minutes to determine how it allows you to enter command level statements via T-SQL.