link server having two different database and also different login

Question

link server having two different database and also different login

biren

Right there with Babe

Points: 719
More actions
June 16, 2010 at 11:37 am

#233906

Hello,
I am using SQL SERVER 2008 and currently I have linked another server on my server, but the problem is this linked server has two different database and has their own sql server authentication login, can anybody please tell me how I can access both the database while I am linked to server.
eg. linked server has two database, database AA who has login A and database BB who has login B.
so when I connect to database AA who has login A and if I want to access database BB I cannot access it as I have to login as login B to access database BB.
can I access both databases once I am linked to server??
If yes, how can I do this?
Any suggestion, Please !!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

Have you already setup the linked Server in the primary server?

That is on Server AA did you go into SSMS and add a linked server under Server objects? If you did then all you need to do is setup a security context. when ever you connect to that linked server it will use that context to connect. so then all you would do is refer to it in your query.

BB.DB.dbo.table

BB=linkedserver name

DB=Databasename

dbo= schema context

tabl= well that chould be obvious

Hope this helps

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

biren Right there with Babe Points: 719 More actions · Answer 2

yes, I have linked the server by going into server objects but here is the problem database AA and database BB has different login but if you go through server objects and add manually the login through properties I can just add one login and password

so, do I have to mention in query??

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

I guess I am confused. On server AA you would add the security contest for BB. this would the the login for BB not the login for AA. so why would you need to add more than one login?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

biren Right there with Babe Points: 719 More actions · Answer 4

ok, here is the explanation

the server to be linked has two databases namely database AA and database BB and both has two different logins, so when I link server using login for database AA I am just able to access this database but my problem is when I link to server I want to have access to both databases.

I hope you understand this !!

Dan.Humphries SSChampion Points: 12125 More actions · Answer 5

See now I get it. The solution is thankfully simple. you can setup more than one linked server to the same instance with different names. Set one up for AA using that login and one up for BB using thaat login. This should allow you to link them and do all the things you need. However if you absolutley need to have access to both at the same time with in a single security context the only way to do that would be to setup or modify access so that you can access both with a single user.

Having two linked server entries should do the trick though.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

biren Right there with Babe Points: 719 More actions · Answer 6

yes I can link more than one server but what if like in my case I have linked the server having database AA with its login details now when I try to link for database BB as both the database is under same server it gives me error saying "the server is already linked"

Lowell SSC Guru Points: 323518 More actions · Answer 7

biren (6/17/2010)
yes I can link more than one server but what if like in my case I have linked the server having database AA with its login details now when I try to link for database BB as both the database is under same server it gives me error saying "the server is already linked"

change the name of the second linked server;

for example MyLinkedServer_DBProduction and MyLinkedServer_DBWarehouse

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 8

from the GUI, it's not obvious how to alias an existing SQL Server:

--#################################################################################################

--Linked server Syntax for SQL Server With Alias

--#################################################################################################

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'DBSQL2K5', @provider = N'SQLOLEDB';

-- Add Default Login (if applicable)

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',

@useself = N'False',

@locallogin = NULL,

@rmtuser = N'sa',

@rmtpassword = 'NotARealPassword';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!