Post reply

MSSQL 2019 - SQL Server Agent - Jobs account

  • December 16, 2020 at 10:28 pm

    #3821383

    Hi everyone,

     

    I have a question regarding the execution of jobs in MSSQL 2019.

    I’m managing several MSSQL servers but I’m not a DBA so maybe my question will sound vapid.

     

    On a SQL mono-instance server, I have a user who is member of “ SQLAgentUserRole” on the database “msdb”.

    He created jobs and is owner of these jobs.

    For what I read (and understood), jobs are executed with the service account running the service “SQL Server Agent” if and only if, the owner of this job is sysadmin (which is not the case for me).

    Unfortunately, I cannot find MS documentation confirming that.

     

    Could someone please explain me that (MS documentation would be ideal).

     

    Now, let’s assume I understood well, and jobs are running with the account specified as the job owner. What would be the best practice to manage these jobs? Should the user change the owner when he creates a job?

     

    Thank you for your answers.

  • December 16, 2020 at 11:46 pm

    #3821402

    They do make it difficult to find. 😀

    Please see the following link...

    https://docs.microsoft.com/en-us/sql/ssms/agent/manage-job-steps?view=sql-server-ver15#transact-sql-job-steps

    In that section of the article, you'll find the following (emphasis is mine)...

    Transact-SQL job steps do not use SQL Server Agent proxies. Instead, the job step runs as the owner of the job step, or as the SQL Server Agent service account if the owner of the job step is a member of the sysadmin fixed server role.

    That doesn't seem to be a complete answer for your question, though.  Still looking...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 17, 2020 at 12:48 am

    #3821424

    Hi Jeff,

     

    Thank you very much for your answer. So, for my understanding, it confirms how I was understanding that.

    So in my case, the user "Bob" who creates job is not sysadmin, so jobs are running with the account specified as being the owner. By default the owner is the user "Bob" who created the job.

    Now my goal is to avoid any service interruption the day we disable the account of the user "Bob" who created jobs.

    I could create another account "jobexec_acc", granting it just necessary permissions in order to execute jobs.

    In my case, the user "Bob" cannot change the owner of jobs he creates/created. Does it mean a DBA needs to do that?

    Once more, I'm not DBA so I don't know if it is the good way to go for managing SQL servers.

     

    Basically, my question is, do you have recommendation for best practices for managing jobs?

    Thank you

  • December 17, 2020 at 5:24 am

    #3821478

    Only someone with full sysadmin authority can change a job's owner.

    Typically after the job is fully debugged and running normally, sysadmin changes the job owner to a standard job-running account.

    If issues come up in the future, the sysadmin can change the owner back to 'Bob' so that the issues can be debugged and corrected, and then the job can be set to the standard account again.  The potential gotcha here is permissions to resources, since 'Bob' may not have full permissions.  Your shop will have to decide how it wants to handle those situations.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 17, 2020 at 7:45 am

    #3821498

    Hi Scott,

    Thank you very much for this explanation. It makes sense and in the same time I was hoping to not have this answer.

    Thank you Jeff and Scott for your time.

    Julien

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply