Permissions required for INNER JOIN across multiple servers?

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?

THANKS !!!
LVL 1
drgdrgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dduserCommented:
Check out the details in the Security of Link Server, Authentication level you have given and Local Login/Remote User details.

Regards,

dduser
SireesCommented:
<<I've looked at Security logins, linked servers, creating new logins and using them on the new server, etc >>

Does this new login has permissions on that table.
drgdrgAuthor Commented:
I must be doing something wrong.

Let me ask this - what account should I be setting on the remote server.  IN other words, should I look at Services, see what account SQL Server is running under locally and make sure that account exists on the foreign server?

Or is there a better way for me to configure the local server with an arbitrary user (johndoe) who is configured on the remote machine?

Locally, what user does the server use to make the connection to the remote, and remotely, where should I be looking for the user account that is accepted in bound?

Thanks
SireesCommented:
Right click on linked servers ->Security and see which account is used

from BOL:

Local login

Specify the local login IDs that can connect to the linked server.

Impersonate

Specify that the local login ID will be used to connect to the linked server. Select this option if you are certain that the local login ID exactly matches a login ID with sufficient permissions on the linked server.

Remote user

Use the remote user to map users not defined in Local login.

Remote password

Specify the password used to map users who are not defined in Local login.

Not be made

Specify that for logins not defined in the list, a connection will not be made.

Be made without using a security context

Specify that for logins not defined in the list, a connection will be made without using a security context.

Be made using the login's current security context

Specify that for logins not defined in the list, a connection will be made using the current security context of the login.

Be made using this security context

Specify that for logins not defined in the list, a connection will be made using the login and password specified in the Remote login and With password boxes.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
drgdrgAuthor Commented:
Thank you for all of the detail.  It is working now.  I also changed the notation from: serverB.dbo.databaseB.tableB
to: serverB.databaseB.dbo.tableB

and a tip for anyone reading this - instead of testing in the app, you can test the connection to the remote server through query analyzer -
just do a SELECT * from serverB.databaseB.dbo.tableB and once security is right, this will work

Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.