Using xp_cmdshell can be quite helpful and sometimes possibly the only answer to some scenarios. I've read some posts on the internet that enabling xp_cmdshell might jeopardize the security of the database/server.

My question is that, is there anything we can do to reduce the risk? For example can we set some restrictions/applying users role, etc providing safeguards to mitigate the risk?


  • 2
    I am not with you on the following: "possibly the only answer to some scenarios". If you share your scenarios, you might get solutions not involving xp_cmdshell. We never use it in our systems.
    – A-K
    Feb 13, 2013 at 1:48

2 Answers 2


Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.

First, make sure xp_cmdshell is enabled for the instance:

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
EXEC sp_configure 'show advanced options', 0;

Now depending on operating system and SQL Server service account, you may need to set up a proxy account (and to do this, thanks to UAC, you may need elevation and launch SSMS as an administrator):

EXEC master..sp_xp_cmdshell_proxy_account 'Domain\User', 'Password';

Then create a wrapper in your database that does whatever you need it to do (this is just a sample of a VERY generic wrapper that doesn't really protect you from anything; only using it to demonstrate):

USE yourdb;
CREATE PROCEDURE dbo.uxp_cmdshell
  @cmd VARCHAR(2048)
  EXEC master.dbo.xp_cmdshell @cmd;

Now give your user or role permissions to execute the procedure:

GRANT EXECUTE ON dbo.uxp_cmdshell TO [your_user_or_role!];

Now when your_user logs in, and tries to execute willy-nilly calls to xp_cmdshell:

EXEC master.dbo.xp_cmdshell 'dir c:\';

They will get:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

However if they pass the same command to your new procedure:

EXEC dbo.uxp_cmdshell 'dir c:\';

It will work just fine (assuming your proxy account is set up correctly and/or the SQL Server service account has adequate permissions).

This can take some work, but effectively this allows you to control exactly what your users can do with xp_cmdshell.

  • Thanks. It seems what I was looking for. Could you please shed some light on how to do that? Do you mean I should enable xp_cmdshell in the stored proc where it's been called before using and then disable it right after the calling statement (when I'm done with that)?
    – Sky
    Feb 12, 2013 at 23:33
  • No, that tactic would not work - what if one person starts the procedure, enables xp_cmdshell, and before they call it, a different user had called the stored procedure and already hit the point where they disable it? Feb 12, 2013 at 23:35
  • I see your point Aaron. You're right.
    – Sky
    Feb 13, 2013 at 2:46

You can also setup a proxy account using sp_xp_cmdshell_proxy_account to allow non-administrators to use xp_cmdshell. This will allow you to setup a less privileged Windows account rather than xp_cmdshell always using the SQL Server service account. This is similar to setting up proxy accounts for SQL Agent jobs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.