This stored procedure is used to create or update mappings between a login on the local instance of SQL Server and login on a remote server. The whole point here is to effectively manage security access between a local login and access by a user to a remote server from the local login. This stored procedure is useful in providing access management in a distributed database envrionment.
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
--returns 0=ok 1=failed
Examples below assume the linked server is named AcctsSEA
Connect a specific login to the linked server by using different user credentials
EXEC sp_addlinkedsrvlogin 'AcctsSEA', 'false', 'Domain\JoeDokes',
'JoeD', 'abc2333'
GO
-- Note! password is not sent encrypted so it could end up written
-- to logs, or compromised in other ways.
Connect ALL local logins to the linked server
EXEC sp_addlinkedsrvlogin 'AcctsSEA', 'true'
The local server needs to have statistics from the linked server in order to best optimize a query. The logged in user requesting the DML operation may not have adequate security for this to happen.
Instead of having to use this stored procedure for mapping, the SQL Server can use the Windows name and password provided that a) the user is connected via Windows Authentication mode; b) Security account delegation is available on both the remote and local servers; c) Windows authentication mode is supported.
Collapse All 
Expand All