Sometimes we use xp_cmdshell in T-SQL to run console applications on a remote server.

“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. [REF]

Now not just anyone can run this extended stored procedure. Out of the box xp_cmdshell is disabled. If you want to use xp_cmdshell you need to enable it. There are a number of ways to enable xp_cmdshell. One of the ways to enable xp_cmdshell is to use the “sp_configure” extended stored procedure using the following TSQL code:

EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO

By default xp_cmdshell can only be used by members of the server role sysadmin. If you want logins that are not members of the sysadmin group to use xp_cmdshell you can do this by setting up proxy credentials for xp_cmdshell. By default the credentials for xp_cmdshell are turned off. Therefore in order to enable proxy credentials you need to run the system extended stored procedure named “sp_xp_cmdshell_proxy_account”.

Here is the syntax for this extended stored procedure:

sp_xp_cmdshell_proxy_account [ NULL | { ‘account_name’ , ‘password’ } ]

Where “account_name” is a Windows login. Here is an example of how to create a xp_cmdshell proxy account for a Windows Account:

EXEC sp_xp_cmdshell_proxy_account [MyDomain\SQLServerProxy], ‘test123123’

When you don’t pass any parameters to sp_xp_cmdshell_proxy_account, this tells SQL Server to remove the proxy account.

EXEC sp_xp_cmdshell_proxy_account NULL;

After enabling the proxy, we can grant a specific user to access the xp_cmdshell:

create login cmdshell with password = ‘test1’
go
use master
go
create user cmdshell for login cmdshell
go
grant execute on xp_cmdshell to cmdshell
go

By using the created user, it will be granted full access to the xp_cmdshell and execute any console application installed on the remote server [REF]