Use xp_cmdshell stored procedure as a non-sysadmin account

There are some situations where you'll need to use XP_CMDSHELL in SQL 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:

  1. Create a Active Directory user account and create a SQL login for that account
  2. 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’;
  3. 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];
  4. Run the following query to confirm your new proxy account is working:
    1. EXECUTE AS LOGIN = 'user'
    2. GO
    3. EXEC xp_cmdshell 'whoami.exe'
    4. REVERT
  5. That should return the AD account you configured in step 2

reference: http://sweetsql.blogspot.com/2011/06/execute-xpcmdshell.html