Edit

sp_addremotelogin (Transact-SQL)

  • Article

Applies to: SQL Server

Adds a new remote login ID on the local server. This enables remote servers to connect and execute remote procedure calls.

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use linked servers and linked server stored procedures instead.

Transact-SQL syntax conventions

Syntax

sp_addremotelogin
    [ @remoteserver = ] N'remoteserver'
    [ , [ @loginame = ] N'loginame' ]
    [ , [ @remotename = ] N'remotename' ]
[ ; ]

Arguments

[ @remoteserver = ] N'remoteserver'

The name of the remote server that the remote login applies to. @remoteserver is sysname, with no default. If only @remoteserver is specified, 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. This is added by using sp_addserver. When users on @remoteserver connect to the local server that is running 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 = ] N'loginame'

The login ID of the user on the local instance of SQL Server. @loginame is sysname, with a default of NULL. @loginame must already exist on the local instance of SQL Server. If @loginame is specified, all users on @remoteserver are mapped to that specific local login. When users on @remoteserver connect to the local instance of SQL Server to execute a remote stored procedure, they connect as @loginame.

[ @remotename = ] N'remotename'

The login ID of the user on the remote server. @remotename is sysname, with a default of NULL. @remotename must exist on @remoteserver. If @remotename is specified, the specific user @remotename is mapped to @loginame on the local server. When @remotename on @remoteserver connects to the local instance of SQL Server to execute a remote stored procedure, it connects as @loginame. The login ID of @remotename can be different from the login ID on the remote server, @loginame.

Return code values

0 (success) or 1 (failure).

Remarks

To execute distributed queries, use sp_addlinkedsrvlogin.

sp_addremotelogin can't be used inside a user-defined transaction.

Permissions

Only members of the sysadmin and securityadmin fixed server roles can execute sp_addremotelogin.

Examples

A. Map one to one

The following 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

The following 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

The following 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';

Related content