title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | helpviewer_keywords | monikerRange | |||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Security Role Requirements for Replication |
Learn about the authentication level necessary for common replication setup tasks and for common replication maintenance tasks in SQL Server. |
MashaMSFT |
mathoma |
09/25/2024 |
sql |
replication |
how-to |
|
|
=azuresqldb-mi-current||>=sql-server-2016 |
[!INCLUDEsql-asdbmi] 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 [!INCLUDEssNoVersion] 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. |