Mystical_Ice
asked on
SQL 2008R2 Linked Server - Can access one database but not the other?
Hi
Two servers, each with an instance of sql 2008r2.
Server 1: HOUSQL2\INSTANCE
Server 2: HOUSQL2TEST\INSTANCE
Different SA passwords, but I created a link from server 2 to server 1, using SQL authentication.
I can run the following from server 2:
select top 100 * from [housql2\instance].databas
and I get records returned. But if I run:
select top 100 * from [housql2\instance].databas
I get the error: Msg 208, Level 16, State , Line 1 Invalid object name 'database2.dbo.coss_workor
The database does exist, and I can see it on server 1, and navigate to it.
Another thing that's weird, is on server 2, even if I delete the link to server 1, and can verify by running "sp_linkedservers" and confirming server 1 doesn't show up in the list, I can still run the first above command and get records. Although there's no linked server in the list (both in the GUI and by running sp_linkedservers), I can still pull up records from the first database on the server.
Any ideas?
Two servers, each with an instance of sql 2008r2.
Server 1: HOUSQL2\INSTANCE
Server 2: HOUSQL2TEST\INSTANCE
Different SA passwords, but I created a link from server 2 to server 1, using SQL authentication.
I can run the following from server 2:
select top 100 * from [housql2\instance].databas
and I get records returned. But if I run:
select top 100 * from [housql2\instance].databas
I get the error: Msg 208, Level 16, State , Line 1 Invalid object name 'database2.dbo.coss_workor
The database does exist, and I can see it on server 1, and navigate to it.
Another thing that's weird, is on server 2, even if I delete the link to server 1, and can verify by running "sp_linkedservers" and confirming server 1 doesn't show up in the list, I can still run the first above command and get records. Although there's no linked server in the list (both in the GUI and by running sp_linkedservers), I can still pull up records from the first database on the server.
Any ideas?
arnold
The credentials you used to establish tge link does not have rights on the other db. Check the server security, logins that you use for the link to make sure it has rights on the sevo do db as it does on the first.
ASKER
The linked server is setup to link using sa, which has rights on all databases
Double check that it is not simply your query is using a different server than you expected.
I.e. you have ssms with connection to both servers, you opened a new query thinking it was to server2 but it is actuallly server1.master.
I.e. you have ssms with connection to both servers, you opened a new query thinking it was to server2 but it is actuallly server1.master.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mystical_Ice, do you still need help with this question?
ASKER
No thank you. The solution I posted was the final result
ASKER
This was the final resolution