SQL Remote User's Guide
  A Tutorial for Adaptive Server Enterprise Users
    Tutorial: Adaptive Server Enterprise replication
      Setting up the consolidated database

Create the necessary users and permissions

A set of users and permissions are required for SQL Remote installations. In this tutorial, the following are required:

• A remote user or subscriber, with name field_user.

• A publisher user name, called hq_user.

This section describes the steps you need to take to create each user and assign them the necessary permissions.

To create the publisher

1. Add a login called hq_user, with hq as the default database and with system administrator access:

   exec sp_addlogin hq_user, hq_pwd, hq
   go
   exec sp_role 'grant', sa_role, hq_user
   go

2. Add the login name as a user to the HQ database:

   use hq
   go
   exec sp_adduser hq_user
   go

3. Make this user the publisher of the HQ database:

   exec sp_publisher hq_user
   go

Add a remote user 

Each remote database is identified in the consolidated database by a user ID with REMOTE permissions. Whether the remote database is a single-user server or a database server with many users, it needs a single user ID to represent it to the consolidated database.

In a mobile workgroup setting, remote users may already be users of the consolidated database, and so no new users would need to be added; although they would need to be set as remote users.

When a remote user is added to a database, the message system they use and their address under that message system need to be stored along with their database user ID.

To create the subscriber

1. If you do not have a login name that you can use for the remote user, add a login:

   exec sp_addlogin field_user, field_pwd, hq
   go

2. Add a user to the hq database:

   exec sp_adduser field_user
   go

3. Grant the user remote permissions. Execute the sp_grant_remote stored procedure, using field_user as the user name, file as the message type, and the appropriate directory as the address:

   exec sp_grant_remote field_user, file, field
   go

   As with the publisher address, the address of the remote user (field) is a directory relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run. You should run the Message Agent from your tutorial directory for the addresses to be interpreted properly.

   For information about setting the SQLRemote value, see Setting message type control parameters.