There are some situations where you’ll need to use XP_CMDSHELL in Microsoft SQL Server but you don’t want to just grant the SQL user sysadmin access to your SQL server.
The trick is to create a “proxy” account that SQL will use automatically when your non-sysadmin user tries to execute the XP_CMDSHELL stored procedure.
The process to do this is pretty simple:
- Create a Active Directory user account and create a SQL login for that account
- Configure SQL to use that account when non-sysadmin SQL accounts try to use XP_CMDSHELL by running the following query:
EXEC sp_xp_cmdshell_proxy_account ‘yourdomain\yournewADuser’, ‘password’;
- Configure SQL so that your non-sysadmin user can use the proxy account (automatically, no changes to code required) when attempting to execute the xp_cmdshell stored procedure. Run the following query (after replacing [user] with your non-sysadmin SQL user account):
GRANT EXEC ON xp_cmdshell TO [user];
- Run the following query to confirm your new proxy account is working:
EXECUTE AS LOGIN = 'user' GO EXEC xp_cmdshell 'whoami.exe' REVERT
The result should return the AD account you configured in step 2