xp_cmdshell Extended Stored Procedure

Executes a given command string as an operating-system command shell and returns any output as rows of text.

Syntax

xp_cmdshell command_string [, no_output]

where

command_string: Specifies the command string to execute at the operating-system command shell.
no_output: Is an optional parameter that executes the given command_string but does not return any output to the client.

Remarks

Be aware that when you grant execute permission for xp_cmdshell to users, the users will be able to execute any operating-system command at the Windows NT command shell that the account running SQL Server (typically local system) has privilege to execute.

To restrict xp_cmdshell access to users who have administrator permission on the Windows NT-based computer where SQL Server is running, use SQL Setup or SQL Enterprise Manager to set the server options, selecting the "xp_cmdshell - Impersonates Client" option. With this option selected, only users who have connected to SQL Server via a trusted connection and are members of the local Administrators group on that computer are allowed to use xp_cmdshell. The commands run by xp_cmdshell continue to execute in the server's security context.

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. Do Not Return Output

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

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.

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

Permission

Execute permission defaults to the system administrator, who can grant permission to other users.