The following error is received when a user that is not sysadmin is trying to execute the xp_cmdshell stored procedure:

exec xp_cmdshell 'dir c:'

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

 

Use the bellow code to grand execute permission to that user on the xp_cmdshell stored procedure:

USE [master]
CREATE USER [username] FOR LOGIN [username]
GRANT
      Execute
      ON [dbo].[xp_cmdshell]
      TO [username]

 

After, trying again to execute the xp_cmdshell as the non-sysadmin user, the following error appears:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

 

This happens because when the xp_cmdshell stored procedure is executed by a non-sysadmin user, it connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account## which doesn?t exits.

 

...

 

The proxy account credential can be created by executing the following code:

EXEC sp_xp_cmdshell_proxy_account
              'DOMAIN_NAME\USER_NAME'
              ,'password'

 

Also, it can be created from SSMS (SQL Server Management Studio), but make sure to use the exactly same name for the credential, otherwise the xp_cmdshell still will not work or the creation of the credential will fail:

To see the non-sysadmin users that can execute the xp_cmdshell stored procedure, execute the following code:

use master
exec sp_helprotect 'xp_cmdshell'

To see the proxy acount?s settings, execute the following code:

    select *
    from   sys.credentials
    where  name in ('##xp_cmdshell_proxy_account##')

 

 


1 comment

Comment from: Salvador Ahumada [Visitor]
Salvador AhumadaExcellent, i finally was able to make it work, thanks for sharing this information
08/25/15 @ 18:52

Leave a comment


Your email address will not be revealed on this site.
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)