No databases are displayed under a linked server

I have a bunch of linked servers created on SQL Server Server3. Each one of them, when I expand, has a folder "Catalogs", then when I expand that folder, i see the list of databases on that linked server. But one server - Server2, when I expand it, I see folder Catalogs but when I expand it, I only see "System catalogs" and "dafault' but not the list of all databases.When I log in to that server directly, I see all the databases, but not via linked server.

Can someone help me troubleshoot the issue?
LVL 35
YZlatAsked:
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.

COANetworkCommented:
Make sure you are using a login in the linked server definition that has access to your user databases.  You can impersonate, use a network logon, etc.  Your issue is permissions.
0
YZlatAuthor Commented:
COANetwork, I already checked, I am able to login to the target server using the login that is in the linked definition. When loging in directly, i am able to login and see all the databases. Why does not it allow me to do the same thing through the linked server?

I also tried expanding "default" folder and there were "tables" and 'views", and if I click on tables or views, I get 'error received when attempting to access linked server is "Login failed for user 'test_user'  Error: 18456"

I know test_user exists on target server and if i login using test_user, I am able to see all the databases
0
Randy Knight, MCMPrincipal ConsultantCommented:
The previous commenter is right.  This is permission.  Double check the password.  Try scripting out the linked server and dropping and re-creating that way.  This way you can see the password right in the script so you know it is right.
0
YZlatAuthor Commented:
Here is my solution:

Wen to Setup --> Add Targets. Chose Auto Discovery Results to see if the database has been discovered. Clicked on Promote, and now it can be seen in Targets-->Databases.
0

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
YZlatAuthor Commented:
found the solution myself
0
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 2008

From novice to tech pro — start learning today.