Hi Everyone I am involved in a development project where we are hoping to use Microsoft SQL Server replication. The system will consist of clients running a custom application and MSDE 2000. The client MSDE installations will be subscribed to a merge publication on a central server SQL 2000 server. The users will typically work in disconnected mode and be able to initiate synchronization from the custom application when they can connect to the main server. The replication is being configured using Enterprise Manager and every effort is being made to stick with Windows Authentication (although most replication guides/documents on the Internet seems to use SQL authentication).
For the purpose of this explanation, assume there is a table in the database called AppAdmin containing data that certain users must be allowed to change (insert, update and delete via stored procedures created for this purpose). These changes need to be propagated to all users. Regular users must not be allowed to change this table. Anyone is allowed to make changes to the data in other tables and these changes must be propagated to all users. The difficulty is that the users (please correct me if I am wrong) need to have the sysadmin role on their local database to initiate the synchronization. They can be prevented from making certain changes to their local databases via the application, but there is nothing to prevent them from using another database access tool, making changes and then synchronizing with the central database - and the changes then being propagated to other users. The AppAdmin table cannot be removed from the publication as then the required users would not be able to update it - and additionally the other users would not receive the changes. A couple solutions considered so far (that did not seem optimal) are as follows: 1. Create 3 publications instead of 1: A merge publication without the AppAdmin table for regular users. 2. A snapshot publication of only the AppAdmin table for regular users. 3. A merge publication of the whole database for the 'admin' users. Set the allowed users for each of these publications. 2. Use a stored procedure to initiate the synchornization that a non sysadmin user would have permission to execute. (Not sure how to do this or if this is possible). What is the best way to deal with this situation? Any help, advice or references to useful documentation would be appreciated. Thanks.
