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_addlinkedsrvloginSQL 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:
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_droplinkedsrvloginYou 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:
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_helplinkedsrvloginTo 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:
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 |