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.
