none
Replication- SQL jobs owner

    Question

  • in our replication setup ,

    some jobs running under one windowsuser  account "  domain1\ABC  "

    we are going to remove this account,  so we need to change this to some other admin user. how to perform this , bcoz below repl-distribution  running continuously,

    shall i change it to job's property - owner parameter ? in wizard. 

    Thursday, August 30, 2018 6:12 AM

All replies

  • Hi KIRUBAKARAN,

    In your scenario, did you mean that you want to change the owner of SQL Server Agent Job?

    If so, I think you can try following script:

    USE msdb ; GO EXEC dbo.sp_manage_jobs_by_login @action = N'REASSIGN', @current_owner_login_name = N'danw', @new_owner_login_name = N'françoisa' ; GO

    --OR

    DECLARE @name_holder VARCHAR(1000)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT [name]  FROM msdb..sysjobs 
    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    exec msdb..sp_update_job
            @job_name = @name_holder,
            @owner_login_name = 'sa'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor


    Reference: https://docs.microsoft.com/en-us/sql/ssms/agent/give-others-ownership-of-a-job?view=sql-server-2017 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bf0afcbe-1125-408c-99fb-42827a06ef97/update-job-owner-for-all-sql-agent-jobs?forum=sqldatabaseengine 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 30, 2018 7:53 AM
    Moderator
  • As previous answer you can do it by T-SQL script or with SSMS. We always change owners of replication job to sa after we setup replication to avoid such issues.
    Thursday, August 30, 2018 8:18 AM

  • all above jobs are replication jobs ... currently running... so i can directly change owner to sa ? right ? in wizard(SSMS).  hope this will not affect replication setup?


    Thursday, August 30, 2018 11:24 AM
  • Just go to job properties and change Owner on the General tab:

    On certain jobs that run continuously, like log reader in transactional replication, you may need to stop the log reader agent, change job owner, then restart the log reader agent, in order for change to take effect immediately.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, August 30, 2018 12:23 PM
  • yes that would work. in order to change be effective you need to restart the jobs (Distribution agents and Log REaders).
    Friday, August 31, 2018 7:13 AM
  • ok thats for log_reader  jobs..

    but my owner is in repli-distribution & replic-snapshot ... this also require to restart of jobs?    not  SQL & agent services? 

    Wednesday, September 12, 2018 12:36 PM
  • ok thats for log_reader  jobs..

    but my owner is in repli-distribution & replic-snapshot ... this also require to restart of jobs?    not  SQL & agent services? 

    Yes, you can change the job owner for the distribution agent and snapshot agent jobs. However, there should be no need to run the "snapshot agent" job after the owner change. This is because snapshot normally runs once in a lifetime (unless you frequently reinitialize your subscriptions). So, the next run of the snapshot should use the new owner.

    Another thing I wanted to point out is, by doing the above, you are simply changing the job owners but the agents actually use the account you specified during the initial setup to run i.e. the account that shows up in the agent job's second step "Run Agent". If that account is the user being deleted, you should change the account there too. To know what account is being used by the agents, you can check the second step "Run agent" and check the tab "Run As".

    You may also check the publication properties by right-clicking the publication-->Properties-->Agent Security. If you see the account which you are going to delete, you will need to update the new account in the agent security too.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, September 12, 2018 1:40 PM