How to Solve xp_cmdshell for Non-System Users

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
EXEC sp_configure ‘xp_cmdshell’, 1

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’
use master
create user cmdshell for login cmdshell
grant execute on xp_cmdshell to cmdshell

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]


One thought on “How to Solve xp_cmdshell for Non-System Users

  1. Thanks Suha, I’d seen this elsewhere, but your succinct last part (“we can grant a specific user to access the xp_cmdshel” onwards) is what got me to the finish line with a working solution.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s