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 setup tasks:
|
Setup task |
Membership requirement |
|---|---|
|
Enable a Distributor, Publisher, or Subscriber. |
sysadmin server role on the Publisher. |
|
Enable a database for replication. |
sysadmin server role on the Publisher. |
|
Create a publication. |
db_owner database role on the publication database at the Publisher or sysadmin server role on the Publisher. |
|
View publication properties. |
Member of the PAL at the Publisher, db_owner database role on the publication database at the Publisher, or sysadmin server role on the Publisher. |
|
Create a subscription. |
db_owner database role on the publication database at the Publisher or sysadmin server role on the Publisher. db_owner database role on the subscription database at the Subscriber or sysadmin server role on the Subscriber. |
|
Configure agent profiles. |
sysadmin server role on the Distributor. |
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. |
