SQL SERVER – Fix: The EXECUTE Permission was Denied on the Object ‘xp_cmdshell’, Database ‘mssqlsystemresource’,schema ‘sys’.

I write and play around a lot with SQL object level permissions and I love working with them. Having said that, I also explore the ways people want to run code to achieve certain objectives. In the DBA’s world when the number of servers we are talking is not a single digit, they are looking for ways to automate and script out. Recently I was at a retail company backed team in India and they said they have close to 1500+ databases which are running at their various outlets and point of sale counters and the DBA team working to manage these were still less than 10. I was pleasantly surprised by this level of details. To achieve certain tasks, they have to allow non-sysadmin users to run xp_cmdshell. Here is the issue which was they wanted to solve.

When we try to run a simple stored procedure that use xp cmdshell, it fails with below error:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 [Batch Start Line 0] The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.

They followed error message and the granted permission to execute the stored procedure but got next error

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 3] 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.

WORKAROUND/SOLUTION

I explained the reason and risks involved in using xp_cmdshell and they wanted to use proxy account. I provided below link which explains about it.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql

Here are the complete steps which can fix the issue for others.

1. Create proxy for xp_cmdshell. This is a mapping to provide account under whose credential xp_cmdshell will run. It is advisable that this should NOT be a part of a windows local administrator group else it would be like shooting in the foot.

EXEC sp_xp_cmdshell_proxy_account 'SQLAuthority\Pinal', 'Password for the user given here'

2. Grant execute to the user

GRANT EXECUTE ON xp_cmdshell TO foo1

In short, the proxy account needs to be a windows account. Then any non-sysadmin user (windows account or SQL account) needs public access to master and execute permission on xp cmdshell.

Reference: Pinal Dave (http://blog.SQLAuthority.com)