Security Role Requirements for Replication

Replication restricts the specific actions that a user can perform based on the roles to which the user's login is mapped. Replication has granted certain permissions to the sysadmin fixed server role, the db_owner fixed database role, and the logins in the publication access list (PAL).

The following table summarizes the authentication level necessary for common replication maintenance tasks:

Maintenance task

Membership requirement

Modify or drop a Distributor, Publisher, or Subscriber.

sysadmin server role on the appropriate server.

Modify or drop a publication.

db_owner database role on the publication database at the Publisher or sysadmin server role on the Publisher.

Modify or drop a subscription at the Publisher.

db_owner database role on the publication database at the Publisher or sysadmin server role on the Publisher.

Modify or drop a subscription at the Subscriber.

db_owner database role on the subscription database at the Subscriber or sysadmin server role on the Subscriber.

Mark a subscription for reinitialization.

Push subscription: db_owner database role in the publication database at the Publisher or sysadmin server role on the Publisher.

Pull subscription: db_owner database role in the subscription database at the Subscriber or sysadmin server role on the Subscriber.

View replication activity, errors, and history using Replication Monitor. A user cannot modify agent profiles, schedules, and so on, unless the user is a member of the sysadmin server role.

replmonitor database role on the distribution database at the Distributor or sysadmin server role on the Distributor.

Maintain replication agents.

db_owner database role in the appropriate database or sysadmin server role on the appropriate server.

If the agent was created by a user in the sysadmin role, and a proxy account was not specified for the agent, the agent runs under the context of the SQL Server Agent account. In this case, a user in the db_owner role cannot modify the job associated with the agent.

Start or stop a replication agent.

Owner of the agent job or sysadmin server role on the appropriate server.