Asked by:
Replication- SQL jobs owner

-
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.
Question
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
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. -
-
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?
- Edited by KIRUBAKARAN J A Thursday, August 30, 2018 11:25 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
-
-
-
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.