Execute xp_cmdshell

Now: we don't enable xp_cmdshell lightly, as it allows a degree of control over the operating system that renders the SQL server vulnerable. However, when we do enable it, it can only be executed by members of the sysadmin role. Sometimes, this isn't quite what we want: we only want the account to be able to execute xp_cmdshell, not to be able to muck about with other SQL system settings.

So.

First, make sure that you have SA access on the server yourself. Then make sure the account that you want to use has access to the relevant databases on the SQL server. It can be a SQL user account or a Windows account: it doesn't matter. For the purpose of this snippet, I'm using a SQL account called 'user'. I'm going to go out on a limb, and guess that you already know how to create an SQL user account.

Then, grant the user execute rights on xp_cmdshell

GRANT EXEC ON xp_cmdshell TO [user];

Next, we need a proxy account which does have sysadmin access, and we'll use this account to run xp_cmdshell to run calls by users who are not members of the sysadmin role. This account must be a windows account. Give it access only to those drives and folders which it requires for the purposes of executing anticipated xp_cmdshell calls.

The proxy created is ##xp_cmdshell_proxy_account##, and in order to create it, you need both the Windows Login which has sysadmin access and its password.

EXEC sp_xp_cmdshell_proxy_account ‘domain\sysadminlogin’, ‘password’;

Check that this has created by querying the sys.credentials table

SELECT * FROM sys.credentials

Finally, verify that the SQL user has access to execute xp_cmdshell with the following code

EXECUTE AS LOGIN = 'user'
GO
EXEC xp_cmdshell 'whoami.exe'
REVERT

The result should be the Domain\Sysadmin account specified earlier.