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.

SQL SERVER - Fix: The EXECUTE Permission was Denied on the Object 'xp_cmdshell', Database 'mssqlsystemresource',schema 'sys'. errorxp_cmdshell-800x261

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

Solarwinds

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'
  1. 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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Error Installing Microsoft Visual Studio 2010 Shell
Next Post
SQL SERVER – FIX: Msg 35250, Level 16, State 7 – The Connection to the Primary Replica is Not Active. The Command Cannot be Processed

Related Posts

3 Comments. Leave new

  • Why do you grant access to foo1 and not ‘SQLAuthorityPinal’ in your example?

    Reply
  • He’s done that because foo1 is the actual user that wants to execute xp_cmdshell. The ‘SQLAuthorityPinal’ user is the proxy account that the spawned xp command will run as when executed by foo1.

    Reply
  • GRANT EXECUTE ON xp_cmdshell TO foo1 fails with:

    Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

    Reply

Leave a Reply

Menu