This post is to answer a question by Mr. Q (I am masking the name of the reader as it does not really matter) as he was trying to use the script in “T-SQL Script to restore backup files from a folder in SQL Server“.
Once, I had a detailed chat with on the error, I understood that Mr.Q was trying to use the script with SQL Login which is not a sysadmin.Hence, he was not able to successfully execute it.
Lets quickly see how to provide xp_cmdshell access to a SQL login as below:
1. Provide GRANT access to user for xp_cmdshell
create login testlogin with password = 'p@$$word' go use master go create user testlogin for login testlogin go grant execute on xp_cmdshell to testlogin
2. Create proxy account credentials for Login to use a windows authentication
Once you have granted xp_cmdshell access to the user and tried to execute the xp_cmdshell, you may end up with the below error:
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.
This is because the user is not a member of sysadmin fixed server role. In this case, xp_cmdshell tries to connect to Windows by using a windows account stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential has not provided, xp_cmdshell will fail again. The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. This stored procedure takes a Windows user name and password as arguments to the procedure.
EXEC sp_xp_cmdshell_proxy_account 'windowsaccount', 'account_password';
With these changes, the query worked for him and he was happy!