In this article, I create a login for a linked server in SQL Server. In this case I use Transact-SQL (as opposed to using a GUI such as SSMS).

To create a login for linked server, use the sp_addlinkedsrvlogin system stored procedure.

Syntax

The syntax goes like this:

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'   
     [ , [ @useself = ] { 'TRUE' | 'FALSE' | NULL } ]   
     [ , [ @locallogin = ] 'locallogin' ]   
     [ , [ @rmtuser = ] 'rmtuser' ]   
     [ , [ @rmtpassword = ] 'rmtpassword' ]

The following example demonstrates how this works. For a more detailed explanation of each argument, see the official Microsoft documentation.

Example 1 – Add a Login for the Linked Server

Here’s an example of adding a SQL Server login called Maggie for a linked server called Homer:

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

Run this on the local server after you’ve created the linked server. You’ll need a corresponding login with the same credentials on the actual remote server.

Example 2 – Include the Linked Server

For the sake of completeness, here it is again, but this time I create the linked server first:

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=NULL, 
    @rmtuser=N'Maggie', 
    @rmtpassword=N'BigStrong#Passw0rd';

As long as the actual (remote) linked server has a login with the same credentials, the local server will be able to log in to the linked server.

Example 3 – Add the Login/User on the Remote Server

As mentioned, you’ll need to make sure there’s a corresponding login on the remote server. You’ll also need to ensure that this login maps to a database user with the appropriate permissions.

So in this case, we could create a login on the remote server by jumping over to the remote server and running this:

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

USE Music;
CREATE USER Maggie FOR LOGIN Maggie;

GRANT SELECT ON DATABASE::Music TO Maggie; 

This creates a login called Maggie and a corresponding database user called Maggie. It also grants SELECT permissions to Maggie for the Music database.

Now that I’ve done that, any distributed queries using this login will be limited to the Music database.

An alternative to using GRANT SELECT ON DATABASE, is to add the user to the db_datareader fixed database role, or perhaps to a custom role.

If you created the linked server before you created the logins and database users on the remote server, you may need to drop the linked server and create it again. If so, you can drop it like this:

EXEC sp_dropserver 'Homer', 'droplogins';

Once dropped, you can create the linked server, and its associated login again.

Do I Really Need a Linked Server Login?

It’s possible that you don’t even need to add a login like in the above example.

When you first 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’re good to go. No need to add a login for the linked server.

Having said that, you may have other reasons to add a separate login, such as adding it for other users.

You might also want to create a login mapping for a single local user, in which case you can use the @locallogin argument to specify the local login to be mapped.

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