0

I have a local SQL Server 2012, and a vendor has a remote SQL Server 2012 setup for us with a read only SQL user. The vendor will do nothing except provide this read only login. I can login and query with remote credentials all day long. But, when connected to my instance, I can't query the vendor instance. I need to query and run jobs from my server, but can't.

Here's some info on what I can and can't do:

  • Login with their credentials and run queries:

    • Server: Some.I.P.Address\SomeName
    • U: ReadOnlyUser
    • P: ThePassword

  • I Created a linked server with my local logins and the above remote user which will pass a connection test.

  • From my server I can query a remote system table, but not the remote user tables:

    • Meaning I CAN NOT do this:

    Select top 1 * from [Some.I.P.Address\SomeName].DateBaseName.ReadOnlyUser.[TableName]

    • But I CAN do this:

    SELECT top 1 * FROM [Some.I.P.Address\SomeName].master.sys.Servers

How come I can connect directly to the remote instance and run queries, but when I am connected to my server I can only query the system tables?

It gives me this error, but A) the table does exist, and B) the user does have permissions because I can connect directly with the same credentials that pass the linked server connection test and run a query on that table.:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "[Some.I.P.Address\SomeName]" does not contain the table ""DateBaseName"."ReadOnlyUser"."TableName"". The table either does not exist or the current user does not have permissions on that table.
glitzsfa
  • 353
  • 1
  • 2
  • 14
  • Can the read only user only query from the schema owned by the read only user, or from all schema and it just defaults to something other than dbo (the default)? If the user name is RDR and the table name is CONTACT the Table might exists as DBO.CONTACT and not as RDR.CONTACT – Brad D Dec 23 '15 at 14:38
  • Have you mapped the local user to the user on the other server via sp_addlinkedsrvlogin? – Tom H Dec 23 '15 at 14:47
  • Brad D - I've tried a number of different names before the table name, .dbo., .mylocaluser., .ReadOnlyRemoteUser. and nothing is different. It weird that I can only query the remote sys tables while connected to my local instance. – glitzsfa Dec 23 '15 at 15:21
  • Tom H - I tried adding my local user as you suggested. EXEC sp_addlinkedsrvlogin '[Some.I.P.Address\SomeName]', '', '', 'mylocaluser', 'mylocalpassword' says that the user doesn't have permissions to perform this action. So does just this: EXEC sp_addlinkedsrvlogin '[Some.I.P.Address\SomeName]' – glitzsfa Dec 23 '15 at 15:22

0 Answers0