1. Customer wants to use a non-sysadmin user — Test to execute ‘xp_cmdshell’. Below scripts works fine in my testing environment.
USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.credentials WHERE [name] = ‘##xp_cmdshell_proxy_account##’)
BEGIN
CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = ‘domainuser’, SECRET = ‘xxxx’
END
GO
USE [master];
GO
EXEC sp_grantdbaccess ‘test’;
GO
GRANT EXEC ON xp_cmdshell TO test;
GO
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.