Creates or updates a mapping between logins on the local server running Microsoft® SQL Server™ and remote logins on the linked server.
sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
[,[@useself =] 'useself']
[,[@locallogin =] 'locallogin']
[,[@rmtuser =] 'rmtuser']
[,[@rmtpassword =] 'rmtpassword']
0 (success) or 1 (failure)
When a user logs in to the local server and executes a distributed query that accesses a table on the linked server, the local server must log in to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log in to the linked server.
A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the local login’s user credentials when connecting to the linked server on behalf of the login (equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server). Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.
Rather than having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use the Windows NT security credentials (Windows NT username and password) of a user issuing the query to connect to a linked server when all these conditions exist:
After the authentication has been performed by the linked server using the mappings defined by executing sp_addlinkedsrvlogin on the local SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.
sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.
Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlinkedsrvlogin.
This example creates a mapping to ensure that all logins to the local server connect through to the linked server Accounts using their own user credentials.
EXEC sp_addlinkedsrvlogin 'Accounts'
Or
EXEC sp_addlinkedsrvlogin 'Accounts', 'true'
This example creates a mapping to ensure that all logins to the local server connect through to the linked server Accounts using the same login SQLUser and password Password.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'
This example creates a mapping to ensure that all logins to the local server connect through to the linked server mydb without using a login or password (mydb does not require a login or password).
EXEC sp_addlinkedsrvlogin 'mydb', 'false', NULL, NULL, NULL
Or
EXEC sp_addlinkedsrvlogin 'mydb', 'false'
This example creates a mapping to ensure that only the Windows NT user Domain\Mary connects through to the linked server Accounts using the login MaryP and password NewPassword.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'NewPassword'
This example first creates a linked server named ExcelSource, defined as the Microsoft Excel spreadsheet DistExcl.xls, and then creates a mapping to allow the SQL Server login sa to connect through to ExcelSource using the Excel login Admin and no password.
EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
Security for Linked Servers | sp_droplinkedsrvlogin |
Configuring Linked Servers | System Stored Procedures |
sp_addlinkedserver |