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. 

Answer 1

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 [e-mail].

Answer 2

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.

Answer 3

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?

Answer 4

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

Answer 5

yes that would work. in order to change be effective you need to restart the jobs (Distribution agents and Log REaders).

Answer 6

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? 

Answer 7

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.