Authorizes a new remote server user by adding an entry to master.dbo.sysremotelogins.
sp_addremotelogin remoteserver [, loginame [, remotename] ]
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.
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_remotelogin.
sp_addremotelogin GATEWAY, ralph, pogo
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.
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.
System Administration Guide for more information about setting up servers for remote procedure calls and for using “unified login.”
isql in the Utility Guide
The permission checks for sp_addremotelogin differ based on your granular permissions settings.
Setting | Description |
---|---|
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. |
Values in event and extrainfo columns from the sysaudits table are:
Information | Values |
---|---|
Event | 38 |
Audit option | exec_procedure |
Command or access audited | Execution of a procedure |
Information in extrainfo |
|