Setting Up Remote Users

When you add a remote user, you must add both the login ID and the remote login ID on the server being set up as the user's remote server. The login ID is the ID on the server that is set up as the user's remote server. The remote login ID maps the login ID on the remote server to the login ID on the user's local server.

    To add a login ID

1. Using SQL Enterprise Manager, select the server set up as the user's remote server.
2. From the Manage menu, choose Logins.
3. From the Manage Logins dialog box, add a new login ID and password.

This login ID will be used as the remote user's login ID on this remote server.

To add a remote login ID, you use sp_addremotelogin to map the user's login ID on the user's local server to the new login ID on this remote server.

    To add a remote login ID

• Type:

  sp_addremotelogin remoteserver [, login_ID [, remotename]]

  where

  remoteserver

  Specifies the server to which the remote login applies. This is the user's local server. For example, if you're logged in to AIRLINES and want to add a remote login, RESERVATION is the remoteserver.

  login_ID

  Is the login ID that identifies the remote login. This is the login ID added using SQL Enterprise Manager¾for example, manage, account, agents. If you don't specify login_ID or remotename, all users will have the same remote and local login IDs (this inserts a -1 in the sysremotelogins table).

  remotename

  Is the login ID that maps to the login_ID. This is the user's login ID for his or her local server,¾for example, margie, dan, and robin. If you don't specify remotename, all users are mapped to the login_ID you specified (this inserts a NULL in the sysremotelogins table).

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 an entry in sysremotelogins that matches the remote server name and the remote login ID. If one is found, then the local server login ID for that row is used to log in the remote user.
• If no entry is found, the local server searches for an entry that has a remote login ID of NULL and a local login ID that is not -1. In this case, the remote user is mapped to the local server login ID.
• If the previous attempts fail, the sysremotelogins table is checked for an entry that has a remote login ID of NULL and a local login ID of -1. In this case, whatever remote login ID was supplied by the remote server is used to look for a local login ID in the syslogins table.

The following examples show the different ways to add remote users. If you want to change from one method to the other, you must use sp_dropremotelogin to remove the old mapping, as described later in this chapter in Dropping Remote Users.

For example, suppose all the users in your business use the local server ACCOUNTS. You set up the EXPENSES server as a remote server for ACCOUNTS. Before users can access remote procedures on EXPENSES from ACCOUNTS, you must give them remote login IDs.

You must use the remote server EXPENSES to do the following procedures. The first example maps a user to a particular login ID.

    To map the user who has the login ID of chris on the ACCOUNTS server to the login ID of salesmgr on the EXPENSES server

1. Use SQL Enterprise Manager add the salesmgr login ID to the EXPENSES server.

   From the Server Manager window, select EXPENSES. From the Manage menu, choose Logins. From the Manage Logins dialog box, add the salesmgr login ID.

2. Use sp_addremotelogin to map salesmgr to chris.

   sp_addremotelogin accounts, salesmgr, chris

The next example maps all users to one remote login ID.

    To map all of the users on the ACCOUNTS server to the salesgroup login ID on the EXPENSES server

1. Use SQL Enterprise Manager to add the salesgroup login ID to the EXPENSES server.

   From the Server Manager window, select EXPENSES. From the Manage menu, choose Logins. From the Manage Logins dialog box, add the salesgroup login ID.

2. Use sp_addremotelogin to map salesgroup to all of the users on the ACCOUNTS server.

   sp_addremotelogin accounts, salesgroup

   This inserts a NULL in the sysremotelogins table. Note that you can only map users on a server once with a NULL.

The last example maps all users to the same local and remote login IDs.

    To map all the users on the ACCOUNTS server to identical login IDs on the EXPENSES server

1. Use SQL Enterprise Manager to add the login IDs of the users to the remote server.

   From the Server Manager window, select EXPENSES. From the Manage menu, choose Logins. From the Manage Logins dialog box, add the following login IDs: john, steve, mary, and john.

2. Use sp_addremotelogin to map them to their respective login IDs on the ACCOUNTS server.

   sp_addremotelogin accounts

   This inserts NULL in the remoteusername column in the sysremotelogins table and - 1 in the suid column.

   You can map users only once with a null value. You can have only one NULL value per server in the sysremotelogins table.

When you add a login ID, the login ID is entered in the syslogins table. When you add a remote login ID, SQL Server checks the syslogins table and then adds the remote login ID to the sysremotelogins table. When you view the sysservers table, the local server (the server you are logged in to) has a srvid (server identification) of 0.