Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Login For Linked Server

Author  Topic 

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2014-04-15 : 07:17:15
Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB.

ServerB has two databases on it and both using different logins to access. Say DB1 has Login1 and DB2 has Login2

So can I create something like this:


EXEC master.dbo.sp_addlinkedserver @server = N'db_link', @srvproduct=N'SQLServer OLEDB Provider', @provider=N'SQLNCLI', @datasrc=N'ServerB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'Login',@rmtuser=N'Login1',@rmtpassword='Password1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'login',@rmtuser=N'Login2',@rmtpassword='Password2'


Am I able to access two different databases using this same linked server. I tried but with no success. Is there any way around it?[code]

------------------------------------------------
The answer is always no till than you don't ask.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-15 : 11:18:11
maybe two different linked servers. Each pointing to the same instance but with different security settings.

Be One with the Optimizer
TG
Go to Top of Page

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2014-04-16 : 00:42:49
That is true we can make something like this. But what I really wanted to know is that scenario explained above is possible or not in SQL Server?

quote:
Originally posted by TG

maybe two different linked servers. Each pointing to the same instance but with different security settings.

Be One with the Optimizer
TG



------------------------------------------------
The answer is always no till than you don't ask.
Go to Top of Page

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2014-04-16 : 00:42:50
That is true we can make something like this. But what I really wanted to know is that scenario explained above is possible or not in SQL Server?

quote:
Originally posted by TG

maybe two different linked servers. Each pointing to the same instance but with different security settings.

Be One with the Optimizer
TG



------------------------------------------------
The answer is always no till than you don't ask.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-16 : 10:09:09
well the way have it coded in your original post I would say no. Because the local login is the same for both linked server versions - there is no way sql could tell which remote login to use.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -