Mapping Local Logins to Logins on Linked Servers

To gain access to a linked server, the linked server must validate the user for security reasons. The requesting server (that is, the local server) provides a login name and password to the linked server on behalf of the local server user. For this to work, you need to map the local logins with the linked server logins you are going to use.

sp_addlinkedsrvlogin

SQL Server provides the sp_addlinkedsrvlogin system stored procedure to map local logins to logins on the linked servers. This stored procedure can be executed by members of the sysadmin and securityadmin fixed-server roles.

 sp_addlinkedsrvlogin [@rmtsrvname =] '  rmtsrvname  '   [,[@useself =] '  useself  '][,[@locallogin =] '  locallogin  ']  [,[@rmtuser =] '  rmtuser  '] [,[@rmtpassword =] '  rmtpassword  '] 

The following list describes each element of the syntax:

rmtsrvname	The linked server that will use this login setting.
useself	The setting that determines whether a user or group of users will use their own usernames and passwords to log in to the linked server. There are two possible settings:
	'true'	Local server logins use their own usernames and passwords to log in to the linked server. Consequently, the rmtuser and rmtpassword arguments are ignored. For example, the local jdoe user with a password of shrek would attempt to log in to the linked server with the jdoe username and the shrek password.
	'false'	Local server logins will use the arguments specified in rmtuser and rmtpassword to log in to the linked server. For a linked server that does not require usernames and passwords (such as Microsoft Access), these arguments can be set to NULL .
locallogin	Specifies which local logins are affected by this mapping. You can designate either an individual login or all local logins. To specify that all logins be affected, pass a NULL to this argument.
rmtuser	The username that will be used to connect to the linked server if @useself is set to FALSE .
rmtpassword	The password that will be used to connect to the linked server if @useself is set to FALSE .

By default, after you run sp_addlinkedserver , all local logins will automatically attempt to use their own usernames and passwords to log in to the new linked server. Essentially, SQL Server runs the following statement after sp_addlinkedserver :

 EXEC sp_addlinkedsrvlogin @rmtsrvname='My_Linked_Server',  @useself='true', @locallogin=NULL 

You can delete this default mapping with sp_droplinkedsrvlogin , which is described in the next section.

In NT authentication mode, SQL Server will submit the NT username and password to the linked server if the provider supports NT authentication and security account delegation is available on both the client and server.

The following example will connect all users to the 'ORACLE_DATABASE' linked server using the 'guest' username and 'confio' password:

 EXEC sp_addlinkedsrvlogin @rmtsrvname='ORACLE_DATABASE',  @useself='false', @rmtuser='guest', @rmtpassword='confio' 

This example will connect all users to the 'SQL_SERVER_DB ' linked server using their own local usernames and passwords:

 EXEC sp_addlinkedsrvlogin @rmtsrvname='SQL_SERVER_DB',  @useself='true' 

This example will log in the local 'RobinOrdes' user as the remote user 'ROrdes' with the 'new_orleans' password to the 'ORACLE_DATABASE' linked server:

 EXEC sp_addlinkedsrvlogin @rmtsrvname='ORACLE_DATABASE',  @useself='false', @locallogin='RobinOrdes', @rmtuser='ROrdes', @rmtpassword='new_orleans' 

This example will log in the Windows NT user 'Domain1\DonLarson' as the remote user 'DLarson' with the 'five_sons' password:

 EXEC sp_addlinkedsrvlogin @rmtsrvname='ORACLE_DATABASE',  @useself='false', @locallogin='Domain1\DonLarson', @rmtuser='DLarson', @rmtpassword='five_sons' 

This example will connect all users to the 'ACCESS_DATABASE' linked server without providing a username or password:

 EXEC sp_addlinkedsrvlogin @rmtsrvname='ACCESS_DATABASE',  @useself='false', @rmtuser=NULL, @rmtpassword=NULL 

sp_droplinkedsrvlogin

You can delete mappings for linked servers using sp_droplinkedsrvlogin . Members of the sysadmin and securityadmin fixed-server roles can execute this stored procedure:

 sp_droplinkedsrvlogin [@rmtsrvname =] '  rmtsrvnam  e',  [@locallogin =] '  locallogin  ' 

The following list describes each element of the syntax:

rmtsrvname	The linked server that will lose this login mapping.
locallogin	The local login that will lose the mapping to the linked server. You can designate either an individual login or all local logins. To specify that all logins should be affected, pass a NULL to this argument.

This first example removes the login mapping for the 'RobinOrdes' user to the 'ORACLE_DATABASE' linked server:

 EXEC sp_droplinkedsrvlogin @rmtsrvname='ORACLE_DATABASE',   @locallogin='RobinOrdes' 

This example removes the default login mapping for all users using the 'SQL_SERVER_DB' linked server:

 EXEC sp_droplinkedsrvlogin @rmtsrvname='SQL_SERVER_DB',  @locallogin=NULL 

sp_helplinkedsrvlogin

To determine the current linked server login settings, run the sp_helplinkedsrvlogin procedure:

 sp_helplinkedsrvlogin [[@rmtsrvname =] '  rmtsrvname  ',]   [[@locallogin =] '  locallogin  '] 

The following list describes each element of the syntax:

rmtsrvname	The linked server that will have its login settings displayed.
locallogin	The local login mappings that will be displayed.

The first example shows the sp_helplinkedsrvlogin output if no arguments are provided. It displays one line for each linked server login mapping. The first column ( Linked Server ) shows which linked server owns this mapping. The second column ( Local Login ) shows which user is affected by this mapping. If set to NULL , this mapping applies to all users who do not have a specific mapping. The third column ( Is Self Mapping ) displays a 1 if the local username and password will be attempted on the remote server. If it displays a , the value in the last column ( Remote Login ) will be used to log in to the remote server. Note that the remote password is not listed for security reasons:

 EXEC sp_helplinkedsrvlogin  GO Linked Server              Local Login  Is Self Mapping  Remote Login -------------------------  -----------  ---------------  ------------ ACCESS_DATABASE            NULL         0                NULL ACCESS_SERVER              NULL         1                NULL EXCEL_SPREADSHEET          NULL         1                NULL ODBC_with_DATASOURCE      NULL         1                NULL ODBC_with_PROVIDER_STRING  NULL         1                NULL ORACLE_DATABASE            NULL         0                guest ORACLE_DATABASE            RobinOrdes   0                ROrdes 

The next example shows the sp_helplinkedsrvlogin output if only the rmtsrvname argument is provided. The output is identical to the preceding example except only the entries for the specified server are displayed:

 EXEC sp_helplinkedsrvlogin @rmtsrvname='ORACLE_DATABASE'  GO Linked Server    Local Login  Is Self Mapping  Remote Login -------------    -----------  ---------------  ------------ ORACLE_DATABASE  NULL         0                guest ORACLE_DATABASE  RobinOrdes   0                ROrdes 

The final example shows the sp_helplinkedsrvlogin output if all arguments are provided. Again, the output is identical to the previous examples except that it is limited to the server and is user specified:

 EXEC sp_helplinkedsrvlogin @rmtsrvname='ORACLE_DATABASE',   @locallogin='RobinOrdes' GO Linked Server     Local Login  Is Self Mapping  Remote Login ----------------- -----------  ---------------  ------------ ORACLE_DATABASE   RobinOrdes   0                ROrdes