Permissions required to run REPLMERG.EXE

Question

We use merge replication in one of our programs and I would like to allow our users to force synchronization of their laptops with the publisher on an as-needed basis (we are using push subscriptions). I got this working using REPLMERG.EXE (see my previous question).

However, when the users trid to run the script they received the following error message:

Only members of the sysadmin or db_owner roles can perform this operation.
...
exec sp_MSreplcheck_subscribe
...

If I add the users' group login as a db_owner on their local subscription database then the script works correctly. The problem is that they also end up with full access to every table in their local database, which is not something we can live with.

Allowing users in a merge replication topology to synchronize their local push subscriptions on-demand without giving them full-blown control of the db seems like a pretty straightforward use case, but I can't get it working.

Answer 1

From Replication Agent Security Model:

Merge Agent for a pull subscription

The Windows account under which the agent runs is used when it makes connections to the Subscriber. This account must at minimum be a member of the db_owner fixed database role in the subscription database.

The account that is used to connect to the Publisher and Distributor must:

• Be a member of the PAL.
• Be a login associated with a user in the publication database.
• Be a login associated with a user in the distribution database. The user can be the Guest user.
• Have read permissions on the snapshot share.

Therefore is a documented requirement of Merge replication that the account running the replication agent (replmerge.exe) be member of db_owner. If you this does not work for you situation, then Merge replication is not the right technology to use, since it has a requirement you cannot fill.

Now int theory an application can do whatever REPLMERGE does from another application, and you can leverage the power of code signing to run a set of wrapper procedures that are granted dbo privileges via code signing, thus not needing the elevated login, but that's just theory since the replication procedures are not exactly easy to use nor are they documented at the level one needs to re-implement the agents...

Answer 2

The suscriber must have the right to replicate data definition instructions sent on the publisher. Some of these instructions might even lead to the reinitialisation of the subscriber, which requires the right to a drop\recreate the corresponding database. In these conditions, security requirements as set by Microsoft sound quite sensible.

Answer 3

As Remus and Philippe have pointed out, db_owner on the subscription db is a hard requirement for synchronizing a merge push subscription. However, we really wanted to allow our users to synchronize their own laptop without giving them full db_owner rights to the database.

Our solution was to enable mixed mode authentication on the subscribers and add a SQL Server user whose sole purpose was to enable our end users to synchronize their laptops. The SQL Server user, 'syncuser', was given the db_owner role on the local subscription database. Then, when we called replmerg.exe from within the program, we specified the following switches:

-SubscriberSecurityMode 0 -SubscriberLogin syncuser -SubscriberPassword 4w3$0m3_P4$$w0Rd