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)