sp_addremotelogin System Stored Procedure

Adds a new remote login ID to master.dbo.sysremotelogins.

Syntax

sp_addremotelogin remoteserver [, login_ID [, remotename]]

where

remoteserver

Is the name of the remote server to which the remote login applies. The server must be known to the local server by an entry in the master.dbo.sysservers table.

login_ID

Is the login ID of the user on the local SQL Server. The user named by login_ID must already exist in the master.dbo.syslogins table. Use the sp_addserver system stored procedure to add a remote server.

remotename

Is the ID used by the remote server when logging in to the local server. All remote logins not explicitly matched to a local ID are automatically mapped to a local ID.

Remarks

When a server receives a remote login, the local server tries three ways to map the remote user to a local user. First, the local server looks for an entry in the sysremotelogins table that matches the remote server name and the remote login ID. If one is found, then the local server's user ID (suid) for that row logs in the remote user. If no entry is found, the local server searches for an entry with a value of NULL in the remoteusername column and a local server's user ID that is not -1. In this case, the remote user is mapped to the local server's user ID. Finally, if the previous attempts failed, the local server checks the sysremotelogins table for an entry with a remote name of NULL and a local server's user ID of -1. In this case, the remote login ID supplied by the remote server looks for a local server's user ID in the syslogins table.

The login ID of the local user may be different on the remote server.

Every remote login entry has a status. The default status is not trusted. This means that when a remote login with not trusted status is received, SQL Server checks the password. If you don't want the password to be checked, change the status to trusted by using the sp_remoteoption system stored procedure.

Examples

A.    Map One to One

This example shows a simple way to map remote names to local names when the local and remote servers have the same users. It creates an entry in the sysremotelogins table so that the remote server ACCOUNTS can validate logins. When no login parameters are given, a null value is entered in the remoteusername column of sysremotelogins.

sp_addremotelogin ACCOUNTS

B.    Map Many to One

This example creates an entry that maps all users from the remote server ACCOUNTS to a local login ID Albert.

sp_addremotelogin ACCOUNTS, Albert

C.    Explicit One-to-One Mapping

This example causes a remote login from the remote user Chris on the remote server ACCOUNTS to be mapped to the local user salesmgr.

sp_addremotelogin ACCOUNTS, salesmgr, Chris

Permission

Only the system administrator can execute this procedure.

See Also