Adds a new remote login ID on the local server, allowing remote servers to connect and execute remote procedure calls.
sp_addremotelogin [ @remoteserver = ] 'remoteserver'
[ , [ @loginame = ] 'login' ]
[ , [ @remotename = ] 'remote_name' ]
[@remoteserver =] 'remoteserver'
Is the name of the remote server that the remote login applies to. remoteserver is sysname, with no default. If only remoteserver is given, all users on remoteserver are mapped to existing logins of the same name on the local server. The server must be known to the local server (added using sp_addserver). When users on remoteserver connect to the local server running Microsoft® SQL Server™ to execute a remote stored procedure, they connect as the local login that matches their own login on remoteserver. remoteserver is the server that initiates the remote procedure call.
[@loginame =] 'login'
Is the login ID of the user on the local SQL Server. login is sysname, with a default of NULL. login must already exist on the local SQL Server. If login is specified, all users on remoteserver are mapped to that specific local login. When users on remoteserver connect to the local SQL Server to execute a remote stored procedure, they connect as login.
[@remotename =] 'remote_name'
Is the login ID of the user on the remote server. remote_name is sysname, with a default of NULL. remote_name must exist on remoteserver. If remote_name is specified, the specific user remote_name is mapped to login on the local server. When remote_name on remoteserver connects to the local SQL Server to execute a remote stored procedure, it connects as login. The login ID of remote_name can be different from the login ID on the remote server, login.
Return Code Values
0 (success) or 1 (failure)
To execute distributed queries, use sp_addlinkedsrvlogin.
Every remote login entry has a status. The default status is not trusted. When a remote login with not trusted status is received, SQL Server checks the password. To not have the password checked, use sp_remoteoption to change the status to trusted.
sp_addremotelogin cannot be used inside a user-defined transaction.
Only members of the sysadmin and securityadmin fixed server roles can execute sp_addremotelogin.
A. Map one to one
This example maps remote names to local names when the remote server Accounts and local server have the same user logins.
EXEC sp_addremotelogin 'ACCOUNTS'
B. Map many to one
This example creates an entry that maps all users from the remote server Accounts to the local login ID Albert.
EXEC sp_addremotelogin 'ACCOUNTS', 'Albert'
C. Use explicit one-to-one mapping
This example maps a remote login from the remote user Chris on the remote server Accounts to the local user salesmgr.
EXEC sp_addremotelogin 'ACCOUNTS', 'salesmgr', 'Chris'