T-SQL query to change the job owners for all jobs owned by a specific login

Many SQL Server DBAs manage jobs through the SSMS GUI and do not know about the underlying T-SQL scripts being run. This blog shows how to use T-SQL to change the job owner for all the jobs owned by a specific login.

Suppose you have a SQL instance with hundred of jobs owned by a specific login [MyDomain\Ram]. The user left the organization, and his AD id is deactivated. You start getting job failure notification- The job failed. The owner (MyDomain \Ram) of job TestJob does not have server access.

Can we change the job owners in one go? Yes, it is possible using the sp_manage_jobs_by_login stored procedure.

The following query updates the SQL Server agent job owners from ‘MyDoamin\Ram’ to SA account.

USE msdb ;
GO
EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'MyDomain\Ram',
@new_owner_login_name = N'SA';
GO

Using the following query, you can get a list of jobs with their name, owners, and status (enabled\disbled).

SELECT s.name AS JobName, l.name AS JobOwner, enabled
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
WHERE enabled=1
ORDER by l.name