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 = 'domain\user', 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 Domain\user , 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. There is a 'deny' on public role.
Posted at https://sl.advdat.com/3eaNEkY
