SQL Server: allow xp_cmdshell to a non-sysadmin login
Enable xp_cmdshell, grant EXECUTE and configure the proxy account for a login without sysadmin rights.
Let a login that is not a sysadmin run xp_cmdshell through a proxy account. xp_cmdshell is disabled by default and runs operating-system commands with the SQL Server service account: enable it only when it is needed, and keep the rights of the proxy account to the minimum. The method is based on a third-party article (Allow a user to execute xp_cmdshell without being a sysadmin).
1. Enable xp_cmdshell
-- check
SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
WHERE name = 'xp_cmdshell'
go
EXEC sp_configure 'Show advanced Options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
-- check
SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
WHERE name = 'xp_cmdshell'
2. Check which Windows account runs the command
When executed by a sysadmin, xp_cmdshell runs under the SQL Server service account.
SELECT SUSER_SNAME()
EXEC xp_cmdshell 'ECHO -----WHO AM I: & whoami & ECHO --------------------------- & DIR C:\'
3. Grant EXECUTE to a login
GRANT EXECUTE ON OBJECT::xp_cmdshell TO app_user
4. Create the proxy account credential
The proxy account is a Windows account. The name ##xp_cmdshell_proxy_account## is fixed.
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##')
DROP CREDENTIAL ##xp_cmdshell_proxy_account##
GO
CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = 'DOMAIN\user', SECRET = '<PASSWORD>'
5. Grant the Windows right "Log on as a batch job"
In the Local Security Policy (secpol.msc): Local Policies > User Rights Assignment > Log on as a batch job. Add the account used as the proxy account.
6. Test
use master
EXECUTE AS LOGIN = 'app_user'
SELECT SUSER_SNAME() [Login at instance level], USER_NAME() [user at database level]
EXEC xp_cmdshell 'ECHO -----WHO AM I: & whoami & ECHO --------------------------- & DIR C:\'
REVERT
Troubleshooting
If the execution fails for the tested login, first check the EXECUTE permission on xp_cmdshell (step 3) and the presence of the proxy credential (step 4).
Notes
- The final
REVERTreturns the session context to the original login after the test. - Passwords and account names are placeholders: never store a clear-text password in a shared script.