I would like to set up merge replication where the users develop and
test data in the subscriber database and then replicate to the
publisher via the ActiveX merge replication control in a web page.
How can I set up security so that the users can only write changes to
the publisher via replication and not via direct modification?

TIA,

I want the users to be able to make data modifications to a subscription
database (staging) and merge them up into the publisher
(production/operational). I also don't want to give the users sysadmin
membership on the subscriber server or even db_owner membership in the
subscription database on that server. That may be a separate issue, but
just as important, if not more. From my testing, I get an error merging
if I am a user that does not have db_owner or sysadmin membership.

-Peter Daniels
-DBA/Data Architect
-Peter.Daniels@spamBgone.cts.cendant.com

*** Sent via Developersdex http://www.developersdex.com ***

Hi Peter,

Thanks for your post. As I understand, you want to deny the permissions
that users directly modify the publication. If I have misunderstood, please
feel free to let me know.

As I understand, you can try to remove the publisher login from the logins
list in the Publication Access List tab on the Publication Properties
windows.
For more information regarding Publication Access Lists, please refer the
following articles on SQL Server Books Online.
Topic: "Publication Access Lists"

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Peter,

Thanks for your feedback. Currently, I understand that you want to prevent
from the direct modification on the publisher database and will not give
the login db_owner and sysadmin membership on the subscriber database. If I
misunderstood, please feel free to let me know.

What permissions do you want to grant to the login on the subscriber
database? The following article describes the fixed database roles. You can
choose and grant to the login based on your requirements.
Topic: "Adding a Member to a Predefined Role" on SQL Server Books Online.

If the subscriber database and the publisher database are not on the same
instance, we can only deploy login for subscriber server and let your users
update the subscriber database using this login.
If not, you need to deny permissions of the login on the publisher database
to prevent from the direct modification on the publisher database.

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

I don't think you understand the issue. Let's just focus on one thing:

In order to allow users to initiate merge replication, they need to be
db_owner in the subscription database or sysadmin on the server. But, I
don't want to give them db_owner. I'm trying to find a way to allow
them to fire up the merge replication agent w/o giving them db_owner in
the subscription database.

-Peter Daniels
-DBA/Data Architect
-Peter.Daniels@spamBgone.cts.cendant.com

*** Sent via Developersdex http://www.developersdex.com ***

You have to insulate the developers from having system administrator roles
on the publisher, for instance park them in the dbo role on the database.
The problem is that if you give them rights to kick off the replication
agent on your publisher, they will then have rights to access the
subscriber.

You will then have to create some app that has a hard coded system
administrator role account in it that will kick off the replication job -
probably by using the replication ActiveX components.

[quoted text, click to view]

I'm trying to avoid SQL Server authentication if at all possible. I'm
wondering why there is no way to give an NT login rights to write data
in the subscription DB and then initiate the merge agent.

-Peter Daniels
-DBA/Data Architect
-Peter.Daniels@spamBgone.cts.cendant.com

*** Sent via Developersdex http://www.developersdex.com ***

Hi Peter,

Thanks for your feedback. I start to understand that you want a way to
allow the user start the merge replication agent without giving them
db_owner on ssubscription database and sysadmin on the server. If I have
misunderstood, please feel free to let me know.

Merge agent basically is a Job. On the SQL Server side, a user who can
execute sp_start_job to start the job and is a member of the sysadmin fixed
role can start any job. A user who is not a member of the sysadmin role can
use sp_start_job to start only the jobs he/she owns. Therefore, it seems
that your requirements are not easily to meet.

For more information regarding this issue, please refer to the following
article on SQL Server Books Online.
Topic: "Role Requirements"
Topic: "sp_start_job"

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

If the user needs to be sysadmin to start the merge replication process,
then I'm in real trouble.

I wish there was a way for me to specify a list of logins that could
start the merge process. I would think this would be a common design.

-Peter Daniels
-DBA/Data Architect
-Peter.Daniels@spamBgone.cts.cendant.com

*** Sent via Developersdex http://www.developersdex.com ***

Hi Peter,

Thanks for your response. I am not sure why you want to grant permissions
of starting the merge replication agent to your users. As I understand,
when the subscription is specified as "continuously", the users which are
not the members of db_owner role are able to change the subscription
database and the changes will be duplicated to publication database
automatically. Does it meet your requirements?

If you insist on granting the permissions of starting the merge replication
agent to your users, the users need to be a member of the sysadmin role or
the job owner. Also, you can try Hilary's suggestion on your side.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

I'm not sure why you are questioning why I want to do this, but I will
try to explain again.

I want the user to make mutiple changes in their subscription database
and be able to test the changes with a test application before pushing
(merging) the changes into the actual operating (publication) database
that several automated apps run against.

Anyhoo. I'm using Hillary's SQL Server security suggestion for now.
Thanks, Hillary. I always get a bad feeling whenever I have to resort
to SQL Server authentication methods, though.

-Peter Daniels
-DBA/Data Architect
-Peter.Daniels@spamBgone.cts.cendant.com

*** Sent via Developersdex http://www.developersdex.com ***