Hi Experts

I want to create linked server to multiple databases on the same server.

I have 2 database servers (ServerA and ServerB) and want to create linked server on ServerB to link multiple databases on ServerA).

I have admin right on ServerB.

I don't have admin right on ServerA but have different accounts to access on each databases on ServerA.

After I created one linked server object to a database, I cannot create another linked server object for another database as it has already used the server name which is ServerA.

Please advise.

Thanks.

Hi Aung,

There is no such option to create a linked server for each database in SQL Server. You can access all the databases on that linked server depending on the security that has been configured @ Linked server. All you need is four part naming convention pointing to the right database as required.

Hope this helps

Thanks

Bhanu

• Edited by bhanu_nz 10 hours 2 minutes ago

Hi Bhanu

Thanks for the suggestion.

My problem is I don't have a single account to access multiple databases but I have multiple accounts to access respective databases.

Is there any way to create multiple linked server for different database on the same server using differnet accounts? or Should I create an account which has permission to access multiple databases?

Thanks.

Hi Aung,

There is no way to create linked server to multiple databases in SQL Server. Only option is to talk to your DB Admin/Create account that has access to the databases required.

Cheers

Bhanu 

You can use alias names when creating your linked server on server A and can create multiple linked servers point to the same server B. However, it is not recommended as it will result in more maintenance if some option needs to be changed or some security permissions need to be updated, then you would have to change for multiple linked servers. However, if this what you want, here is how to create an alias when creating a linked server: http://alexpinsker.blogspot.com/2007/08/how-to-give-alias-to-sql-linked-server.html

Thanks.

EXEC sp_addlinkedserver 'ServerADB1', '', 'SQLNCLI', 'ServerA', @catalog = 'DB1'

EXEC sp_addlinkedserver 'ServerADB2', '', 'SQLNCLI', 'ServerA', @catalog = 'DB2'

etc.

Please look up the details for sp_addlinkedserver as I typed this from memory.

I can certainly see a point in having one linked server per database, as then you don't have to hardcode the database name in the queries, and the linked-server definitions can be changed if the database name changes.