Microsoft® SQL Server™ replication restricts the specific actions that a user can perform based on the role mapped to the user’s login. Replication has granted certain permissions to the sysadmin server role, the db_owner database role, and the logins in the publication access list (PAL).
Only members of the sysadmin server role can set up and configure replication, including:
Only members of the db_owner database role or the sysadmin server role can:
Members of the PAL, and members of the db_owner database role or the sysadmin server role can subscribe, including:
The table summarizes the requirements for each of the major replication actions.
| Replication action | Membership requirement |
|---|---|
| Replication Administration: | |
| Enable, modify or drop a Distributor | sysadmin server role |
| Enable, modify, or drop a Publisher | sysadmin server role |
| Enable, modify, or drop a Subscriber | sysadmin server role |
| Enable a database for replication | sysadmin server role |
| Create or drop a publication | sysadmin server role or db_owner database role |
| Create a push subscription | sysadmin server role or db_owner database role |
| Create a pull subscription | Any login in the publication’s publication access list |
| Drop a subscription | sysadmin or db_owner database role, or the creating login of a pull subscription |
| Updating a publication access list | sysadmin server role or db_owner database role |
| Enable snapshots for FTP downloading using the Internet |
sysadmin server role or db_owner database role |
| Replication Agents: | |
| Configuring agent profile | sysadmin server role |
| Monitor replication agents | sysadmin server role |
| A Merge Agent logging into the Publisher | Login must be in the publication access list of the referenced publication |
| A Distribution Agent or Merge Agent logging into the Distributor |
Login must be in the publication access list of the referenced publication or db_owner database role on the distribution database |
| A Snapshot Agent or Log Reader Agent logging into the Publisher |
sysadmin server role or db_owner database role |
| A Snapshot Agent or Log Reader Agent logging into the Distributor |
sysadmin server role or db_owner database role on the distribution database |
| Agents logging into the Subscriber | sysadmin server role or db_owner database role |
| Replication Tasks: | |
| Cleanup | sysadmin server role or db_owner database role on the distribution database |
| Scheduling jobs | sysadmin server role or db_owner database role on the msdb database |
| Immediate-updating Subscriber: | |
| An immediate-updating Subscriber connecting to the Publisher |
SQL Authentication login in the publication access list at the Publisher |