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'
REVERTThe result should return the AD account you configured in step 2
Thank you for visiting our website and we hope that our instructions for using XP_CMDSHELL in Microsoft SQL Server have been helpful to you. If you found our guide informative and useful, please consider sharing it with your friends and colleagues who might also benefit from this knowledge. Your support in spreading the word about our website is greatly appreciated and will help us continue to provide valuable resources to the world. Thank you again for your time and we look forward to your continued support.
