More on "Breaking Changes in SQL Server 2005 Replication"

We finally launched our external sql server 2005 with merge replication with pull subscriptions. After extensive QA testing, surprising data loss due to filtering, more testing with new parameterized row filter, we finally made it.

Right after we launched the servers (our publisher and distributor) and the subscribers began to synchronize we had a few conflicts we hadn’t seen in any of the test environments.

The conflict cause was CONNECT REPLICATION permission missing on the users; the server data overwrote the client data on each case.

The Books Online documentation is pretty scarce on this new permission. It only says that if you have CONTROL permission at Database level, the CONNECT REPLICATION permission is implied on the database. Also if you have CONTROL SERVER permission at server level, the CONNECT REPLICATION permission is implied as well. Other than that, there isn’t much.

The official article documenting the breaking changes can be found here:

http://technet.microsoft.com/en-us/library/ms143470.aspx

and it does not mention the difference in permissions.

The breaking change here is that this permission that you can set through the Securables item on the user properties is brand new to SQL Server 2005.

On a SQL Server 2000 engine, the Management Studio looks like:

While on SQL Server 2005 the set of permissions for a given database looks like: