SSMS connects, Linked server fails

Question

There's a SQL Server 2012 machine which I have login and password. It's a SQL Server login, not an AD one.

I'm able to login using SSMS. But when I configure a linked server in my SQL Server I get error message saying this login doesn't have access:

Cannot process the object ""DB"."SCHEMA"."TABLE"". The OLE DB provider "SQLNCLI11" for linked server "SERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

Any idea why this is happening?

Update: This is the scripted creation of the linked server:

USE [master]
GO

/****** Object:  LinkedServer [REMOTE_SERVER_HOST]    Script Date: 16/03/2016 13:56:56 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'REMOTE_SERVER_HOST', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REMOTE_SERVER_HOST',@useself=N'False',@locallogin=N'LOCAL_LOGIN',@rmtuser=N'REMOTE_LOGIN',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Answer 1

The called SP in the remote server should have "set nocount on" at the top. This will solve the issue; provided you have all the permissions required set.

Answer 2

Just to be sure, based on these commands:

EXEC master.dbo.sp_addlinkedserver @server = N'REMOTE_SERVER_HOST', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REMOTE_SERVER_HOST',@useself=N'False',@locallogin=N'LOCAL_LOGIN',@rmtuser=N'REMOTE_LOGIN',@rmtpassword='########'

You have created a linked server called REMOTE_SERVER_HOST on LOCAL_SERVER. You have set the permissions up so that if you log in as LOCAL_LOGIN on LOCAL_SERVER it will connect to REMOTE_SERVER_HOST as REMOTE_LOGIN.

Please confirm that you are in fact connecting to LOCAL_SERVER as LOCAL_LOGIN and that the password for REMOTE_LOGIN is correct.

The way you have it set up those are the only circumstances where it will work.