sp_helprotect is very useful in permission issue
Published Dec 18 2021 07:24 PM 3,328 Views
Microsoft

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. 

 

Bob_Cai_4-1639883119686.png

Bob_Cai_5-1639883467896.png

 

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.

 

Bob_Cai_6-1639883847264.png

 

 

 

 

 

 

 

Co-Authors
Version history
Last update:
‎Dec 18 2021 07:24 PM
Updated by: