I faced this error mentioned in title while running stored procedure in local server which updates data in remote server. I was able to fetch data from remote server but couldn't update through linked server. I checked the linked server and access but it was fine.
Then i created linked server back and added remote login for 'local server' in remote server and worked fine.
EXEC sp_AddRemoteLogin 'local server','login name'
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use linked servers and linked server stored procedures instead. Reference