0

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
  • Does the table have any data? You say you can log in to the remote server directly, but can you access the database and object you are attempting to access via linked server? How did you create your linked server - what options did you enable/configure? – Steve Mangiameli Mar 14 '16 at 18:53
  • Put your query too. – Krismorte Mar 14 '16 at 21:22
  • Which login on the destination instance is being used to access the databases via the linked server? – Peter Mar 15 '16 at 10:10
  • Tables in that DB have data. Inside Linked Server I'm able to list the tables, but when I try to execute a SELECT I get the quoted message. What options should I use? I've never configured a linked server using MSSQL login. – Hikari Mar 16 '16 at 16:56
  • What command is executing when you get that error? – RBarryYoung Nov 20 '16 at 19:01
2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.