SQL Server : Change The Owner of A SQL Server Agent Job

Tags : agentjobsownershipusers

This script will change the ownership of all jobs on a SQL Server instance to the sa user. The script utilizes a cursor to run through all the jobs in sequence and change their ownership. This script is useful if you need to perform cleanups such as user removals (users who own Agaent jobs cannot be removed).

Versions

SQL Server 2005+

use msdb
go
DECLARE @job_count INT
SELECT @job_count = COUNT(distinct(SUSER_SNAME(owner_sid)))
FROM msdb..sysjobs WHERE suser_sname(owner_sid) <> 'sa'
IF @job_count > '0'
    BEGIN
        DECLARE @change_job_id VARCHAR(50)
        DECLARE job_id_cursor CURSOR FOR
        SELECT job_id, name    FROM msdb..sysjobs WHERE suser_sname(owner_sid) <> 'sa'
        OPEN job_id_cursor
        FETCH NEXT FROM job_id_cursor
        INTO @change_job_id
        WHILE @@FETCH_STATUS = 0
            BEGIN
                DECLARE @sql_statement NVARCHAR(255)
                EXEC msdb..sp_update_job @job_id = @change_job_id, @owner_login_name ='sa'
        FETCH NEXT FROM job_id_cursor INTO @change_job_id
END
CLOSE job_id_cursor
DEALLOCATE job_id_cursor
END


    

Report Script

Top Users