quote:

---

Originally posted by Bustaz Kool

I have created a linked server named JUNK_LIVE and am having an access issue that I can't resolve. The connection itself tests out fine but when I attempt to use a table via a SELECT query

select *
from [JUNK_LIVE].MyDatabase.dbo.[MyTable])

I get this message:

The OLE DB provider "SQLNCLI" for linked server "JUNK_LIVE" does not contain the table ""MyDatabase"."dbo"."MyTable"". The table either does not exist or the current user does not have permissions on that table.

Either of these queries succeeds:

exec [JUNK_LIVE].MyDatabase.dbo.sp_ExecuteSQL N'select * from dbo.[MyTable]'

select *
from OPENQUERY([JUNK_LIVE], 'select * from MyDatabase.dbo.[MyTable]')

If I log onto the remote server using the same SQL Logon that the linked server is using, there is no problem accessing any table.

The SQL Logon maps to a User in the database that is a member of the db_datareader database role.

Here's where it gets wierd. If I modify the linked server to use a Logon that maps to a User in the db_datareader AND db_readwriter roles, no problem.

I've snorted around the usual sites looking for a solution but have come up dry. Here is the scripted definition of the linked server:

/****** Object: LinkedServer [JUNK_LIVE] Script Date: 02/10/2009 16:49:32 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'JUNK_LIVE', @srvproduct=N'MSSQLSERVER', @provider=N'SQLNCLI', @datasrc=N'<IP Address <ServerName>>'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'JUNK_LIVE',@useself=N'False',@locallogin=NULL,@rmtuser=N'ro_MyServer',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'JUNK_LIVE', @optname=N'use remote collation', @optvalue=N'true'

Any insight is welcomed. No idea too bizarre to be considered.

Many thanks.


=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

---