Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51

    Unanswered: Can't run job owned by other users

    Background:

    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 problem:

    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.

    The question:

    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?


    Thanks in advance for your expert opinions!!

  2. #2
    Join Date
    Aug 2003
    Posts
    2
    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!

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    The user could set a flag in a table and a job runs every minute to look at the flag - if it is set then starts the sync job and resets the flag.

  4. #4
    Join Date
    Aug 2003
    Posts
    2
    Nigel,
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •