I have a new install with push replication (20+ replications). When it was set up the SQL account 'distributor_admin' was created with sysadmin. I did not do the install but I am responsible for ongoing security of the databases. The install team says the account was auto created which is supported by the msdn article Secure the Distributor
According to the msdn article Replication Agent Security Model it looks like after install we can back the permission up to db_owner with a few other considerations.
The Windows account under which the agent runs is used when it makes connections to the Distributor. This account must:
-At minimum be a member of the db_owner fixed database role in the distribution database.
-Be a member of the PAL.
-Have read permissions on the snapshot share.
-Have read permissions on the installation directory of the OLE DB provider for the Subscriber if the subscription is for a non-SQL Server Subscriber.
-When replicating LOB data, the distribution agent must have write permissions on the replication C:\Program Files\Microsoft SQL Server\XX\COMfolder where XX represents the instanceID.
Note that the account that is used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database, or have equivalent permissions if the subscription is for a non-SQL Server Subscriber.
Also note that when using -subscriptionstreams >= 2 on the distribution agent you must also grant the View Server State permission on the subscribers to detect deadlocks.
Per security rules, if the account keeps sysadmin the password needs to be changed regularly.
In this environment we expect additional replications to be added periodically.
Will dialing back the sysadmin for distributor_admin as described by msdn lead to issues?
Best Answer
In theory no, in practice yes.
After testing for several months on an instance running SQL 2014 Enterprise edition, against 20+ replication instances running MySQL, I found:
distributor_admin does not need SA for day to day operations.
If there is a sync issue (like reboot during patching) if distributor_admin does not have SA it will not self recover. When distributor_admin does have SA, replication has always self recovered after patching.