Setting Up DBOs to Manage Publications and Subscriptions

When you set up publication, distribution, and subscription servers for replication, permissions are automatically set that allow the SAs to manage publications and subscriptions. However, before the DBOs of the publication and subscription databases can manage publications or subscriptions, the proper permissions must be set up.

Permissions are set up by creating some necessary SQL Server login IDs, database users, and remote login mappings. Permissions are set up separately for the DBOs of publication and subscription databases. There are two ways to set up permissions for the DBO of a publication database, depending on whether that publication server uses a remote distribution server or acts as its own combined publisher/distributor.

A DBO on a Combined Publisher/Distributor

When a publication server is configured to act as a combined publisher/distributor, to allow the DBO of a publication database to manage publications, you must:

1. Add a database user to the publisher's msdb database for the login ID of the user who is DBO in the publication database.
2. Add a database user to the publisher's distribution database for the login ID of the user who is DBO in the publication database.

If several users in the publication database are aliased to DBO, this must be repeated for each aliased user. Or, all users could be aliased to one user in the msdb database, and to one user in the distribution database.

Database users are set up using SQL Enterprise Manager or the sp_adduser system stored procedure. For detailed information about setting up database users, see Chapter 9, Managing Security.

A DBO on a Publication Server that Uses a Remote Distributor

When a publication server is configured to use a remote distribution server, to allow the DBO of a publication database to manage publications, you must:

1. On the distribution server, add a login ID for the user from the publication server (the user who is the DBO of the publication database). If that user already has a login ID on the remote distribution server, skip this step.

   Add the login ID using SQL Enterprise Manager or sp_addlogin.

2. On the distribution server, to the msdb database, add a database user for the login ID that was added in step 1.

   Add the database user using SQL Enterprise Manager or sp_adduser.

3. On the distribution server, to the distribution database, add a database user for the login ID that was added in step 1.
4. On the distribution server, map the login ID from the publication server to the login ID on the remote distributor.

   Map the login IDs using sp_addremotelogin. For example, if the publication server is named AIREDALE, the login ID on the distribution server is peggysue, and the login ID of the DBO of the publication database is peggy, you would type:

   sp_addremotelogin AIREDALE, 'peggysue', 'peggy'
   

5. On the distribution server, set the trusted remote login option to use integrated security.

   Set the remote login option using sp_remoteoption. For the above example, you would type:

   sp_remoteoption AIREDALE, 'peggysue', 'peggy', 'trusted', true
   

If several users in the publication database are aliased to DBO, this must be repeated for each aliased user. In this case, instead of creating a database user for each one, you could alias all the users to one user in the msdb database, and to one user in the distribution database.

For detailed information about setting up database users, see Chapter 9, Managing Security. For information about setting up remote logins, see Chapter 10, Remote Servers and Users.

A DBO on a Subscription Server

To allow the DBO of a destination database to manage subscriptions, you must use sp_addremotelogin to map the login ID of the DBO on the subscriber to the repl_subscriber login ID on the publisher. This mapping is performed on the publication server. To use integrated security, the trusted remote login option must then be set using sp_remoteoption.

This must be repeated for each publication server that the destination database will subscribe from. If several users in that destination database are aliased to DBO, this must be repeated once for each aliased user.

    To set up permissions allowing the DBO of a destination database on a subscription server to subscribe to the publications from a publication server

• On the publication server, type

  sp_addremotelogin remoteserver, 'repl_subscriber', 'loginID'

  where

  remoteserver

  Is the name of the subscription server. (From the perspective of the publication server that is being administered, the subscriber is a remote server.)

  repl_subscriber

  Is the login ID on the local server. (In this case, it is the special repl_subscriber login ID on the publication server.)

  loginID

  Is the login ID on the subscriber. (The login ID of the user who is DBO of the destination database.)

  For example:

  sp_addremotelogin TERRIER, 'repl_subscriber', 'delaney' 

Then type:

sp_remoteoption remoteserver, 'repl_subscriber', 'loginID', 'trusted', 'true'

For example:

sp_remoteoption TERRIER, 'repl_subscriber', 'delaney', 'trusted', 'true'

For more information about setting up remote logins, see Chapter 10, Remote Servers and Users.