Microsoft‎ > ‎SQL Server‎ > ‎

Use xp_cmdshell stored procedure as a non-sysadmin account

The gadget spec URL could not be found


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:
    EXECUTE AS LOGIN = 'user'
    GO
    EXEC xp_cmdshell 'whoami.exe'
    REVERT
  5. That should return the AD account you configured in step 2

reference: http://sweetsql.blogspot.com/2011/06/execute-xpcmdshell.html
The gadget spec URL could not be found


Comments