Example of Adding Remote Users

Following is an example that shows how remote users are added and mapped on a remote server. The example is provided for overview and orientation. The procedures you need to use to add remote users are more fully explained in Setting Up Remote Users.

This example shows how to set up a remote server for users at a travel agency. RESERVATION is the local server for all users. The remote server is AIRLINES, and you need to give the users on RESERVATION the appropriate remote login IDs. Most of the users are travel agents, and they need access to the same stored procedures from AIRLINES. There are also two accountants, who need access to a different set of stored procedures. And then there is the manager, who needs access to all of the stored procedures. You decide to map all of the travel agents to agents, the accountants to account, and the manager to manage.

Carry out these steps from the remote server AIRLINES:

1. Add the new login IDs on the remote server (AIRLINES):

   Using SQL Enterprise Manager, from the Server Manager window select AIRLINES; from the Manage menu, choose Logins; and from the Manage Logins dialog box, add the following login IDs and passwords.

   manage, password
   account, password2
   agents

2. Use the sp_addremotelogin system procedure to add the remote login IDs for the users (this step maps the login ID on the remote server to the login ID on the local server):

   sp_addremotelogin, reservation, manage, margie
   <execute>
   sp_addremotelogin, reservation, account, dan
   <execute>
   sp_addremotelogin, reservation, account, robin
   <execute>
   sp_addremotelogin, reservation, agents

   The last sp_addremotelogin statement above maps all of the remaining unmapped users on RESERVATION to the remote login ID agents on AIRLINE. It also inserts a NULL value in the sysremotelogins table. You can have only one NULL value per server in the sysremotelogins table.

3. Set agents to trusted so that the servers do not need to verify passwords:

   sp_remoteoption reservation, agents, null, trusted, true

   For more information about setting this option, see Setting the Remote Login Option, later in this chapter.

4. Grant appropriate permissions for the remote procedures for each user.

   For information about granting object permissions, see Chapter 9,Managing Security.

   A remote procedure can also be run by a server application. For more information about server applications, see Microsoft SQL Server Programming Open Data Services.