Link to home
Create AccountLog in
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 !!!
Avatar of dduser
dduser

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

Regards,

dduser
<<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.
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of Sirees
Sirees

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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