Map multiple local SQL logins to just one remote login for Linked Server

Question

Hello, will my following scripts work?

I'm aiming to have 2 different (or more if needed) local logins map to just 1 remote login.

-- CREATE a Linked Server.

EXEC sp_addlinkedserver
@Testta =N'Remote_SQL_Sever_Linked_Server',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'Remote_SQL_Sever_Linked_Server';

-- DROP all local Logins for this Linked Server.

EXEC sp_droplinkedsrvlogin 'Remote_SQL_Sever_Linked_Server', NULL

-- Add one local Login for this Linked Server.

EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'Remote_SQL_Sever_Linked_Server',
@useself=N'FALSE',
@locallogin='Login_Name_1',
@rmtuser=N'Login_Name_1',
@rmtpassword=N'SomeStrongPassword'

-- Add another different local Login for this Linked Server using same remote login.

EXEC sp_addlinkedsrvlogin
@rmtsrvname=N'Remote_SQL_Sever_Linked_Server',
@useself=N'FALSE',
@locallogin='Login_Name_2',
@rmtuser=N'Login_Name_1',
@rmtpassword=N'SomeStrongPassword'

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

>will my following scripts work?

Yes, it will work. I checked this in my environment.

Best regards,
Cathy Ji

---

If the response is helpful, please click "Accept Answer" and upvote it.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Thanks for your quick and excellent detailed proof reply Cathy, much appreciated.