Transact-SQL Reference

xp_cmdshell

Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

Note When executing xp_cmdshell with the Microsoft® Windows® 95 or Microsoft Windows 98 operating systems, the return code from xp_cmdshell will not be set to the process exit code of the invoked executable. The return code will always be 0.

Syntax

xp_cmdshell {'command_string'} [, no_output]

Arguments

'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(255) 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 necessary if any spaces are present in the file paths or program names referenced by 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 executing the given command_string, and does not return any output to the client.

Return Code Values

0 (success) or 1 (failure)

Result Sets

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

xp_cmdshell 'dir *.exe'

The rows are returned in an nvarchar(255) column.

Executing this xp_cmdshell statement returns the following result set:

xp_cmdshell 'dir *.exe', NO_OUTPUT

Here is the result:

The command(s) completed successfully.

Remarks

xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.

When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.

By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

Note In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.

Permissions

Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

Examples

A. Return a list of executable files

This example shows the xp_cmdshell extended stored procedure executing a directory command.

EXEC master..xp_cmdshell 'dir *.exe'

B. Use Windows NT net commands

This example shows the use of xp_cmdshell in a stored procedure. This example notifies users (with net send) that SQL Server is about to be shut down, pauses the server (with net pause), and then shuts the server down (with net stop).

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. Return no output

This example uses xp_cmdshell to execute a command string without returning the output to the client.

USE master
EXEC xp_cmdshell 'copy c:\sqldumps\pubs.dmp \\server2\backups\sqldumps', 
   NO_OUTPUT

D. Use return status

In this example, the xp_cmdshell extended stored procedure also suggests return status. The return code value is stored in the variable @result.

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

E. Write variable contents out to file

This example writes the contents of the current directory to a file named dir_out.txt in the current server directory.

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