When configuring a linked server in SQL Server, setting up the logins can sometimes be confusing. In this article, I aim to provide a high-level overview of how SQL Server maps local logins to remote logins on the linked server.

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.

So if your local login has a corresponding login on the linked server, with the same credentials, and it has the appropriate permissions, you’ll be able to connect using your local login. There’s no need to add a login for the linked server (assuming you’re happy to connect using your own local login).

But if your local login doesn’t have a corresponding login on the linked server (and with the same credentials), the connection will fail.

In such cases, you can use sp_addlinkedsrvlogin to create a login for the linked server so that local logins can connect to the linked server even when they don’t have a corresponding login on the linked server.

Doing this may result in different users being used on the linked server, depending on whether or not they have a corresponding login on the linked server.

For users connected to SQL Server using Windows Authentication Mode, SQL Server can automatically use the Windows security credentials as long as security account delegation is available on the client and sending server, and the provider supports Windows Authentication Mode.

The examples on this page use SQL Server logins (they don’t use Windows Authentication Mode). These examples show the results I get when logging in to a linked server under different scenarios using local SQL Server logins.

Example 1 – Linked Server with No Explicit Login

First I’ll create a linked server called Homer, but I won’t create any associated logins.

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

This automatically creates a default mapping between all logins on the local server and remote logins on the linked server.

Now I’ll try to run the following pass-through query against the linked server using various local logins:

SELECT * FROM OPENQUERY(
    Homer,
    'SELECT 
       CURRENT_USER AS ''CURRENT_USER'', 
       ORIGINAL_LOGIN() AS ''ORIGINAL_LOGIN'''
);

Query results are as follows.

sa

Login failed for user 'sa'.

Login Details: There’s a login called ‘sa’ on both servers, but they have different passwords. Both are members of the sysadmin server role.

Lisa

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| dbo            | Lisa             |
+----------------+------------------+

Login Details: There’s a login called ‘Lisa’ on both servers, and they have the same password. Both are members of the sysadmin server role.

Milhouse

Login failed for user 'Milhouse'.

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

Apu

Login failed for user 'Apu'.

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

Example 2 – Add a Login for the Linked Server

Next, I’ll create a login for the linked server.

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

This login corresponds with a login on the remote server, so a mapping is created between them.

Now each user will run the following query again:

SELECT * FROM OPENQUERY(
    Homer,
    'SELECT 
       CURRENT_USER AS ''CURRENT_USER'', 
       ORIGINAL_LOGIN() AS ''ORIGINAL_LOGIN'''
);

Query results are as follows.

sa

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| Maggie         | Maggie           |
+----------------+------------------+

Login Details: There’s a login called ‘sa’ on both servers, but they have different passwords. Both are members of the sysadmin server role.

Lisa

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| Maggie         | Maggie           |
+----------------+------------------+

Login Details: There’s a login called ‘Lisa’ on both servers, and they have the same password. Both are members of the sysadmin server role.

Milhouse

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| Maggie         | Maggie           |
+----------------+------------------+

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

Apu

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| Maggie         | Maggie           |
+----------------+------------------+

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

So all local logins were able to connect to the linked server. Even the logins that don’t have a corresponding remote login on the linked server were able to connect. This is because they all used the Maggie login. Thanks Maggie!

Example 3 – Restrict the Login

Now I’ll update the login for the linked server so that it is restricted to Milhouse.

But to do this, I’ll need to remove the linked server and create it again. If I don’t do this, SQL Server will use the existing mappings, and I’ll get the same results as above.

EXEC sp_dropserver 'Homer', 'droplogins';

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

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

So in this case I use @locallogin='Milhouse' (instead of @locallogin=NULL like in the previous example). This will add a login mapping for just one local login (Milhouse).

Each user runs the following query again:

SELECT * FROM OPENQUERY(
    Homer,
    'SELECT 
       CURRENT_USER AS ''CURRENT_USER'', 
       ORIGINAL_LOGIN() AS ''ORIGINAL_LOGIN'''
);

Query results are as follows.

sa

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.

Login Details: There’s a login called ‘sa’ on both servers, but they have different passwords. Both are members of the sysadmin server role.

Lisa

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| dbo            | Lisa             |
+----------------+------------------+

Login Details: There’s a login called ‘Lisa’ on both servers, and they have the same password. Both are members of the sysadmin server role.

Milhouse

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| Maggie         | Maggie           |
+----------------+------------------+

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

Apu

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'Apu'.

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

So the key takeaway here is, even when you restrict the login to just one local login, it doesn’t prevent other local logins from connecting to the linked server. If they have a corresponding login on the linked server, then they’ll be able to access it using their own login mapping that was created when sp_addlinkedserver was run.

Example 4 – Actually Restrict it to just One Login

If you really only want to restrict it to just one login and no more, you can use sp_droplinkedsrvlogin to drop all login mappings that sp_addlinkedserver creates before you execute sp_addlinkedsrvlogin.

EXEC sp_dropserver 'Homer', 'droplogins';

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';

Now let’s run the query once again with each login:

SELECT * FROM OPENQUERY(
    Homer,
    'SELECT 
       CURRENT_USER AS ''CURRENT_USER'', 
       ORIGINAL_LOGIN() AS ''ORIGINAL_LOGIN'''
);

Query results are as follows.

sa

Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.

Login Details: There’s a login called ‘sa’ on both servers, but they have different passwords. Both are members of the sysadmin server role.

Lisa

Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.

Login Details: There’s a login called ‘Lisa’ on both servers, and they have the same password. Both are members of the sysadmin server role.

Milhouse

+----------------+------------------+
| CURRENT_USER   | ORIGINAL_LOGIN   |
|----------------+------------------|
| Maggie         | Maggie           |
+----------------+------------------+

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

Apu

Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.

Login Details: This login is only on the local server. There is no corresponding login on the linked server.

Configuration of the Remote Login

Successfully connecting to the linked server is just the first step in the process. Once connected, your ability to do stuff will be affected by the permissions of the remote user that your login is mapped to.

For example, if Maggie was created on the remote server like this:

CREATE LOGIN Maggie
    WITH PASSWORD = 'BigStrong#Passw0rd';

USE Music;
CREATE USER Maggie FOR LOGIN Maggie;

GRANT SELECT ON DATABASE::Music TO Maggie;

All she can do is run SELECT statements against the ‘Music’ database. Therefore, anyone who connects to the linked server using Maggie’s login will be limited to just that.

It’s good practice to only grant the permissions that are required, but no more.

Official Documentation

This article was intended to provide a high-level overview of how logins work with linked servers. There are plenty of other scenarios that I didn’t cover here.

If you’re interested in learning more, check out the following links to Microsoft’s documentation:

• sp_addlinkedserver
• sp_addlinkedsrvlogin
• sp_testlinkedserver
• sp_droplinkedsrvlogin
• sp_dropserver
• OPENQUERY()