We've done inner joins on tables on multiple servers in the past. We're using another SQL server now and are getting permisison issues.
What we're doing is:
Select Top tableA.firstname, tableA.LastName, tableB.Address
from tableA left join serverB.dbo.databaseB.tableB
on tableB.idcode = tableA.idCode
This has worked in the past but on this new server we're getting:
OLE DB provider 'serverB' does not contain table '"dbo"."databaseB"."tableB"'. The table either does not exist or the current user does not have permissions on that table.
The table does exist. I've looked at Security logins, linked servers, creating new logins and using them on the new server, etc.
The "main" query is on the new server, with a join to the old server.
Both are in the same DOMAIN, in enterprise manager I can see and maintain all servers (doing so from each machine to test), etc.
Any suggestions on what I should be checking and where to make this kind of query work?