Avatar of drgdrg
drgdrg
 asked on

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 !!!
Microsoft SQL Server

Avatar of undefined
Last Comment
drgdrg

8/22/2022 - Mon
dduser

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

Regards,

dduser
Sirees

<<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.
drgdrg

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Sirees

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
drgdrg

ASKER
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