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!