Authorizes a new remote server user by adding an entry to master.dbo.sysremotelogins.
sp_addremotelogin remoteserver [, loginame [, remotename] ]
is the name of the remote server to which the remote login applies. This server must be known to the local server by an entry in the master.dbo.sysservers table, which was created with sp_addserver.
This manual page uses the term “local server” to
refer to the server that is executing the remote procedures run
from a “remote server.”
is the login name of the user on the local server. loginame must already exist in the master.dbo.syslogins table.
is the name used by the remote server when logging into the local server. All remotenames that are not explicitly matched to a local loginame are automatically matched to a local name. In Example 1 , the local name is the remote name that is used to log in. In Example 2 , the local name is “albert.”
Creates an entry in the sysremotelogins table for the remote server GATEWAY, for purposes of login validation. This is a simple way to map remote names to local names when the local and remote servers have the same users:
sp_addremotelogin GATEWAY
This example results in a value of -1 for the suid column and a value of NULL for the remoteusername in a row of sysremotelogins.
Creates an entry that maps all logins from the remote server GATEWAY to the local user name “albert”. Adaptive Server adds a row to sysremotelogins with Albert’s server user ID in the suid column and a null value for the remoteusername:
sp_addremotelogin GATEWAY, albert
For these logins to be able to run RPCs on the local server, they must specify a password for the RPC connection when they log into the local server, or they must be “trusted” on the local server. To define these logins as “trusted”, use sp_remoteoption.
Maps a remote login from the remote user “pogo” on the remote server GATEWAY to the local user “ralph”. Adaptive Server adds a row to sysremotelogins with Ralph’s server user ID in the suid column and “pogo” in the remoteusername column:
sp_addremotelogin GATEWAY, ralph, pogo
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_remoteoption 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_remoteoption 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_remoteoption.
The permission checks for sp_addremotelogin differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage any remote login privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role. |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Documents See the System Administration Guide for more information about setting up servers for remote procedure calls and for using “unified login.”
System procedures sp_addlogin, sp_addserver, sp_dropremotelogin, sp_helpremotelogin, sp_helprotect, sp_helpserver, sp_remoteoption
