Role Requirements
Microsoft® SQL Server™ 2000 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).
These tables summarize the requirements for common replication actions.
| Replication administration |
Membership requirement |
| 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. |
| Modify publication properties. |
sysadmin server role or db_owner database role. If the login is in the PAL, a user can view the publication properties as read-only even if the user is not a member of the sysadmin or db_owner roles. |
| Create or delete a push subscription. |
sysadmin server role or db_owner database role. |
| Create a pull subscription. |
sysadmin server role or db_owner database role or any login in the PAL. |
| Delete a pull subscription. |
sysadmin or db_owner database role, or the creating login of a pull subscription. |
| Update a PAL. |
sysadmin server role or db_owner database role. |
| Enable snapshots for FTP downloading using the Internet. |
sysadmin server role or db_owner database role. |
| 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 role. |
| Replication agents |
Membership requirement |
| Configure agent profile. |
sysadmin server role. |
| Monitor replication agents. |
sysadmin server role. |
| At the Publisher, logins for Snapshot Agents, Log Reader Agents, and Merge Agents. |
For pull subscriptions, login must be in the publication access list of the referenced publication. For push subscriptions, login must be member of db_owner (includes sysadmin) in the publication database. |
| At the Distributor, logins for Snapshot Agents, Log Reader Agents, Distribution Agents, and Merge Agents. |
For pull subscriptions, login must be in the publication access list of the referenced publication or db_owner database role on the distribution database. For push subscriptions, login must be member of db_owner (includes sysadmin) in the distribution database. |
| Distribution Agents and Merge Agents logging into the Subscriber. |
For both push and pull subscriptions, the login must be a member of db_owner (includes sysadmin) in the subscription database. |
| Replication agents |
Membership requirement |
| Configure agent profile. |
sysadmin server role. |
| Replication tasks |
Membership requirement |
| Cleanup. |
sysadmin server role or db_owner database role on the distribution database. |
| Schedule jobs. |
sysadmin server role or db_owner database role on the msdb database. |
| Merge data during merge replication. |
The merge process requires an entry for the Publisher in the sysservers table on the Subscriber. If the entry does not exist, SQL Server will attempt to add this entry. If the login used by the Merge Agent does not have access to add the entry (such as db_owner of the subscription database), an error will be returned. |