Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Category Archives: Command Shell

xp_cmdshell allows Windows CMD shell commands to be executed from within SQL Server. This allows a DBA to perform file and OS-level tasks. All the usual CMD tasks can be issued from within a T-SQL query window or from within a SQL Server Agent job step.

xp_cmdshell is disabled by default so needs to be enabled through the Surface Area Configuration utility. Once enabled, CMD commands can be entered and executed as follows:-

EXEC xp_cmdshell 'enter command here'

So, to rename a file, you can run:-

EXEC xp_cmdshell 'ren "C:\my_file.xml" "renamed.xml"'

A large number of commands can be issued. For a comprehensive list, see here.

Permissions can be problematic when running CMD commands from SQL Server. The user that executes xp_cmdshell must have the correct permissions to perform the required action. This means that the user configured to run the SQL Agent service will require relevant permissions if xp_cmdshell is run from a SQL Agent job. Alternatively, a proxy account with sufficient privileges can be configured.

If you do receive operating system errors relating to permissions, then check which Windows account is running the command by executing:-

EXEC xp_cmdshell 'whoami.exe'

Once the user account is known, steps can then be taken to resolve permissions based issues by granting elevated privileges.


Tags: , , , , ,

%d bloggers like this: