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.
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.
Here are the complete steps which can fix the issue for others.
- 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'
- 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)
Why do you grant access to foo1 and not ‘SQLAuthorityPinal’ in your example?
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.
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.
Also needed your other solution for this – had to launch SSMS as an administrator to run the command https://blog.sqlauthority.com/2017/06/14/sql-server-msg-15137-level-16-error-related-sp_xp_cmdshell_proxy_account/