How to Add another Database to A Linked Server Configuration

[JohnBates]

hi everyone,

SQL Server 2005

I have a linked server setup and it works well - but in Management Studio, only that 1 user database appears for that linked server.

I need to select from tables in a different database on that (linked) server.
How can I add another database to my existing linked server??

Thanks, John

 

[gmmastros]

I don't do too much with linked servers, but...

I think all databases are available to the linked server connection. But.... when you connect, you are using certain credentials to do it. It's possible that the login you are using does not have permissions to the database that you need.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnBates]

Thanks for the reply, George.

I have sa permissions and the login credentials associated with this Linked server - that user exists in the database I want to add.

One of the linked servers has 3 Catalogs/databases associated with it. By that I mean in Mgmt Studio you can see 3 database name under that linked server. I don't know how they were able to add more than 1.

I know this is easy - everything is easy once we figure it out

John

 

[JohnBates]

I have a common user that is in several databases on this server. This user is already in the database that I need to add to the linked server. I assumed the linked server would be able to access all the databases becasue I used that user in the Security context setting.

This must be easy... maybe I'm not explaining my question clearly.

Thank, John

 

[JohnBates]

OK I solved it. The user/login on the Linked Server was jacked-up I suppose. After I deleted the user within each database, then deleted the login itself. I recreated the login and recreated the Linked Server and all those databases now are accessible from the Linked Server.

Thanks, John