xp_cmdshell (Transact-SQL)

Syntax

xp_cmdshell { 'command_string' } [ , no_output ]

Arguments

• 'command_string'
  Is the string that contains a command to be passed to the operating system. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

• no_output
  Is an optional parameter, specifying that no output should be returned to the client.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Executing the following xp_cmdshell statement returns a directory listing of the current directory.

EXEC xp_cmdshell 'dir *.exe';
GO

The rows are returned in an nvarchar(255) column. If the no_output option is used, only the following will be returned:

The command(s) completed successfully.

Remarks

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed.

xp_cmdshell can be enabled and disabled by using the Policy-Based Management or by executing sp_configure. For more information, see Understanding Surface Area Configuration and xp_cmdshell Option.

xp_cmdshell Proxy Account

EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'

Permissions

Because malicious users sometimes attempt to elevate their privileges by using xp_cmdshell, xp_cmdshell is disabled by default. Use sp_configure or Policy Based Management to enable it. For more information, see xp_cmdshell Option.

When first enabled, xp_cmdshell requires CONTROL SERVER permission to execute and the Windows process created by xp_cmdshell has the same security context as the SQL Server service account. The SQL Server service account often has more permissions than are necessary for the work performed by the process created by xp_cmdshell. To enhance security, access to xp_cmdshell should be restricted to highly privileged users.

To allow non-administrators to use xp_cmdshell, and allow SQL Server to create child processes with the security token of a less-privileged account, follow these steps:

1. Create and customize a Windows local user account or a domain account with the least privileges that your processes require.

2. Use the sp_xp_cmdshell_proxy_account system procedure to configure xp_cmdshell to use that least-privileged account.

   Note

   You can also configure this proxy account using SQL Server Management Studio by right-clicking Properties on your server name in Object Explorer, and looking on the Security tab for the Server proxy account section.

3. In Management Studio, using the master database, execute the GRANT exec ON xp_cmdshell TO '<somelogin>' statement to give specific non-sysadmin users the ability to execute xp_cmdshell. The specified login must be mapped to a user in the master database.

Now non-administrators can launch operating system processes with xp_cmdshell and those processes run with the permissions of the proxy account that you have configured. Users with CONTROL SERVER permission (members of the sysadmin fixed server role) will continue to receive the permissions of the SQL Server service account for child processes that are launched by xp_cmdshell.

To determine the Windows account being used by xp_cmdshell when launching operating system processes, execute the following statement:

xp_cmdshell 'whoami.exe'

To determine the security context for another login, execute the following:

EXECUTE AS LOGIN = '<other_login>' ;
GO
xp_cmdshell 'whoami.exe' ;
REVERT ; 

Examples

A. Returning a list of executable files

EXEC master..xp_cmdshell 'dir *.exe'

B. Using Windows net commands

CREATE PROC shutdown10
AS
    EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server 
        shutting down in 10 minutes. No more connections 
        allowed.', no_output
    EXEC xp_cmdshell 'net pause sqlserver'
    WAITFOR DELAY '00:05:00'
    EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server 
        shutting down in 5 minutes.', no_output
    WAITFOR DELAY '00:04:00'
    EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server 
        shutting down in 1 minute. Log off now.', no_output
    WAITFOR DELAY '00:01:00'
    EXEC xp_cmdshell 'net stop sqlserver', no_output

C. Returning no output

USE master;
EXEC xp_cmdshell 'copy c:\SQLbcks\AdvWorks.bck
    \\server2\backups\SQLbcks, NO_OUTPUT';
GO

D. Using return status

DECLARE @result int
EXEC @result = xp_cmdshell 'dir *.exe'
IF (@result = 0)
   PRINT 'Success'
ELSE
   PRINT 'Failure'

E. Writing variable contents to a file

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

F. Capturing the result of a command to a file

DECLARE @cmd sysname, @var sysname
SET @var = 'dir/p'
SET @cmd = @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

See Also

Reference

Concepts