LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon