Unsolved
This post is more than 5 years old
2 Intern
•
643 Posts
0
1247
February 29th, 2012 23:00
SQL Server Replication Permission
Are there any other permissions rather than sysadmin role can turn on/off SQL server replication?
No Events found!
Unsolved
This post is more than 5 years old
2 Intern
•
643 Posts
0
1247
February 29th, 2012 23:00
Are there any other permissions rather than sysadmin role can turn on/off SQL server replication?
Top
Michael_Morris
18 Posts
1
March 1st, 2012 06:00
Below are the Role requiremts for replication and maintainence for 2008 R2
Security Role Requirements for Replication Setup
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.
Security Role Requirements for Replication Maintenance
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.
zhaos2
2 Intern
•
643 Posts
0
March 1st, 2012 21:00
Thanks Michael! except sysadmin and db_owner users, can we also add users to PAL (Public Acess List) to assign necessary permissions?