1. Customer wants to use a non-sysadmin user — Test to execute ‘xp_cmdshell’. Below scripts works fine in my testing environment.
IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE [name] = ‘##xp_cmdshell_proxy_account##’)
CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = ‘domainuser’, SECRET = ‘xxxx’
EXEC sp_grantdbaccess ‘test’;
GRANT EXEC ON xp_cmdshell TO test;
2. After running above scripts, customer still got error 229. We confirmed if login using Domainuser , we are able to run xp_cmdshell.
3. It seems user Test has already had the execute permission on xp_cmdshell. But still got ‘permission was denied’. We even captured TTT trace to analyze this issue. Finally, we found sp_helprotect can easily find out root cause.