SQL Server Administration - User/Login Admin

Add Linked Server Login

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.

Syntax - SQL Server 2005/2008

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 
     [ , [ @locallogin = ] 'locallogin' ] 
     [ , [ @rmtuser = ] 'rmtuser' ] 
     [ , [ @rmtpassword = ] 'rmtpassword' ] 

--returns 0=ok 1=failed

Examples - SQL Server 2005/2008

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'
-- 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'

Misc Notes - SQL Server 2005/2008

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.