We have an application that we've created that runs on laptop computers using the MSDE. It, in turn, uses Merge replication to synch data with a 'master' database. The users application sign-on is a SQL Server login. In some cases, more than one user shares a laptop, so there is more than one user account on that laptop.
The user initiates replication from within the application, but we don't want to give the user the sysadmin fixed server role. On laptops with one user, it's not a problem, we simply make the user the owner of the SQL Server Agent Job that performs the synchonization. On laptops with more than onse user, it's a problem since user A can't start a job owned by user B without being in the sysadmin fixed server role.
Is it possible to tweak a users permissions such that they can execute a job owned by another user without giving them access to everything through the sysadmin role?
Sorry, I can't offer you a solution, rather I would be grateful if I could draw on your own experience so far with this problem.
We have an application with a very similar requirements to your own, the salient difference being that we only have and need a single SQL server user on the laptops. Specifically we have the same requirement for users to trigger the merge pull subscription job from our java application. From books online's documentation of sp_start_job, we understand that if this user account has server role sysadmin, this will work (true, we checked this). However we want to avoid this for security reasons. If we try Books Online's alternative of making the sysadmin-less user account the owner of the job, then this doesn't work, even if we set up a proxy for the SQL server agent (the proxy then has sysadmin server role). We get a message from the agent: "Executed as user <our proxy user>: unknown security error - failed".
I would be very interested to know how you got your merge agent job to be triggered "by making the user the owner of the SQL Server job which performs the synchronisation" as it really doesn't work for us. We have tried with both MSDE and a full SQL server 2000 installation on the subscribers (on windows XP)
I would be grateful for any hints as we are really struggling with this one.
Thanks a lot in anticipation!
Thanks a lot for the suggestion. I guess this would be a reasonable workaround. But for the sake of clarity, do you think the behaviour I described is a bug or am I interpreting the documentation wrongly?