sp_addlinkedsrvlogin
Creates or updates a mapping between logins on the local instance of Microsoft® SQL Server™ and remote logins on the linked server.
Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
Arguments
[@rmtsrvname =] 'rmtsrvname'
Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.
[@useself =] 'useself'
Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server. true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).
[@locallogin =] 'locallogin'
Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows NT user. The Windows NT user must have been granted access to SQL Server either directly, or through its membership in a Windows NT group granted access.
[@rmtuser =] 'rmtuser'
Is the username used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.
[@rmtpassword =] 'rmtpassword'
Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
Remarks
When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on 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 on 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:
- A user is connected to SQL Server using Windows Authentication Mode.
- Security account delegation is available on the client and sending server.
- The provider supports Windows Authentication Mode (for example, SQL Server running on Windows NT).
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.
Permissions
Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlinkedsrvlogin.
Examples
A. Connect all local logins to the linked server using their own user credentials
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'
B. Connect all local logins to the linked server using a specified user and password
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'
C. Connect all local logins to the linked server without using any user credentials
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'
D. Connects a specific login to the linked server using different user credentials
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'
E. Connects a specific login to an Excel spreadsheet (the linked server)
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