How to resolve "...Error occurred during the execution of xp_cmdshell"?

Question

The engineers uses this Access based app that takes data from SQL Server 2005.

The users download a file containing details of parts etc make amendments and upload it again. When the file is upload(checked-in) the information such as the modified date, userId etc is stored in SQL Server. When the user tries to check in they face this error:

The users that are working from home and use remote desktop connection to login into their account are the only one facing this error. The users that are on the office network do not get this error.

I tried making a proxy account and granting access to the user but that doesn't work.

It below query, I get the following errors:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'GRANT'.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'SIDNEY\UsersWindowsLoginId'.

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'UsersWindowsLoginId'`

Query:

CREATE LOGIN UsersWindowsLoginId

GRANT EXECUTE ON xp_cmdshell TO 'SIDNEY\UsersWindowsLoginId';

EXEC sp_xp_cmdshell_proxy_account 'UsersWindowsLoginId'
USE master;
GRANT CONTROL SERVER TO 'UsersWindowsLoginId'

GO

Also, this seems to be a recent phenomenon as the users have been working from home for a while now.

Any help is appreciated. Thanks!

Answer 1

First enable advanced option for run xp_cmdshell, for this need to change 1, below query to enable.Try this

USE [DatabaseName]
GO
-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO 

Thanks and Regards Aravind