sp_addremotelogin

Authorizes a new remote server user by adding an entry to master.dbo.sysremotelogins.

Syntax

sp_addremotelogin remoteserver [, loginame [, remotename] ]

Parameters

Examples

Usage

There are additional considerations when using sp_raddremotelogin:
  • When a remote login is received, the local server tries to map the remote user to a local user in three different ways:
    • First, the local server looks for a row in sysremotelogins that matches the remote server name and the remote user name. If the local server finds a matching row, the local server user ID for that row is used to log in the remote user. This applies to mappings from a specified remote user.

    • If no matching row is found, the local server searches for a row that has a null remote name and a local server user ID other than -1. If such a row is found, the remote user is mapped to the local server user ID in that row. This applies to mappings from any remote user from the remote server to a specific local name.

    • Finally, if the previous attempts failed, the local server checks the sysremotelogins table for an entry that has a null remote name and a local server user ID of -1. If such a row is found, the local server uses the remote name supplied by the remote server to look for a local server user ID in the syslogins table. This applies when login names from the remote server and the local server are the same.

  • The name of the local user may be different on the remote server.

  • If you use sp_addremotelogin to map all users from a remote server to the same local name, use sp_remotelogin to specify the “trusted” option for those users. For example, if all users from the server GOODSRV that are mapped to “albert” are to be “trusted”, use sp_remotelogin as follows:
    sp_remoteoption GOODSRV, albert, NULL, trusted, true

    Logins that are not specified as “trusted” cannot execute RPCs on the local server unless they specify passwords for the local server when they log into the remote server. In Open Client™ Client-Library™, the user can use the ct_remote_pwd routine to specify a password for server-to-server connections. isql and bcp do not permit users to specify a password for RPC connections.

    If users are logged into the remote server using “unified login”, these logins are already authenticated by a security mechanism. These logins must also be trusted on the local server, or the users must specify passwords for the server when they log into the remote server.

  • Every remote login entry has a status. The default status for the trusted option is false (not trusted). This means that when a remote login comes in using that entry, the password is checked. If you do not want the password to be checked, change the status of the trusted option to true with sp_remotelogin.

See also:
  • System Administration Guide for more information about setting up servers for remote procedure calls and for using “unified login.”

  • isql in the Utility Guide

Permissions

The permission checks for sp_addremotelogin differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage any remote login privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addlogin
sp_addserver
sp_dropremotelogin
sp_helpremotelogin
sp_helprotect
sp_helpserver
sp_remoteoption