In this article, I configure a linked server in SQL Server so that only a single local login can access it. To do this, I delete any mappings that are automatically created when I create the linked server, before adding a login mapping for the single local login.

Configure the Linked Server & Login Mapping

EXEC sp_addlinkedserver 
    @server=N'Homer', 
    @srvproduct=N'', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'172.17.0.2',
    @catalog='Music';

EXEC sp_droplinkedsrvlogin 'Homer', NULL;

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname=N'Homer', 
    @useself=N'FALSE', 
    @locallogin='Milhouse', 
    @rmtuser=N'Maggie', 
    @rmtpassword=N'BigStrong#Passw0rd';

This example creates a linked server called Homer. It then drops all login mappings that are automatically created, before adding a login for a specific local login called Milhouse.

For our purposes, the relevant line in this example is @locallogin='Milhouse'. This is the line that specifies that the login mapping is to be created for the specified local login (in this case ‘Milhouse’).

Now Milhouse will be the only local login that’s able to connect to the linked server.

How it Works

When you use sp_addlinkedserver to create a linked server in SQL Server, a default mapping between all logins on the local server and remote logins on the linked server is automatically created. SQL Server uses the credentials of the local login when connecting to the linked server on behalf of the login.

This is great if you do actually want these mappings – it saves you from explicitly creating them. But if you don’t want them, you’ll need to use sp_droplinkedsrvlogin to remove them. That’s what I did in the above example.

Once you’ve dropped all automatically created login mappings, you’re free to use sp_addlinkedsrvlogin to add a mapping for a specific local login.

Allowing all Local Logins to Share the Login Mapping

You can also configure it so that all local logins use the same login mapping (so that it’s not just limited to Milhouse, for example). Also, rather than having each local login map to a corresponding remote login, they can use the login that you configure for the linked server. To do this, simply set @locallogin=NULL. This allows all local logins to connect to the linked server, whether or not they have a corresponding remote login on the linked server.

See How Logins Work on Linked Servers for examples of various local logins trying to access a linked server login under different scenarios.