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