Getting execute permission to xp_cmdshell

Question

I am seeing an error message when trying to execute xp_cmdshell from within a stored procedure.

xp_cmdshell is enabled on the instance. And the execute permission was granted to my user, but I am still seeing the exception.

The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’

Part of the issue is that this is a shared cluster, and we have a single database on the instance, so we don't have a full range of admin permissions. So I can't go in and grant permissions, and what-not.

Answer 1

For users that are not members of the sysadmin role on the SQL Server instance you need to do the following actions to grant access to the xp_cmdshell extended stored procedure. In addition if you forgot one of the steps I have listed the error that will be thrown.

1. Enable the xp_cmdshell procedure

   Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.*

2. Create a login for the non-sysadmin user that has public access to the master database

   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'.*

3. Grant EXEC permission on the xp_cmdshell stored procedure

   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'.*

4. Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account

   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.*

It would seem from your error that either step 2 or 3 was missed. I am not familiar with clusters to know if there is anything particular to that setup.