Change the owner on all SQL Agent Jobs

This SQL Script will change the owner of all the jobs on a SQL Server to the owner specified at the top of the script.

/*
Change the owner on all SQL Agent Jobs
@Author William Brown
@History 2011-12-15 Initial Script Write
*/

declare @owner varchar(100)
set @owner = 'SA' -- Set the name of the new owner here

DECLARE @name VARCHAR(1000)
DECLARE @sql NVARCHAR(2000)

DECLARE job_cursor CURSOR FOR
SELECT name
FROM MSDB.dbo.sysjobs j
ORDER BY name ASC;

OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @name
WHILE @@FETCH_STATUS = 0

BEGIN
SET @sql = 'EXEC MSDB.dbo.sp_update_job @job_name = ''' + @name + ''', @owner_login_name = ''' + @owner + ''''

PRINT @sql
EXEC sp_executesql @sql

FETCH NEXT FROM job_cursor INTO @name;
END

CLOSE job_cursor;
DEALLOCATE job_cursor;

Comments

Popular posts from this blog

Monitoring Raspberry Pi Performance Using Cacti

Using NAGIOS to Check the Physical Memory Available on a Windows Host

Error 2738 Occurs When Installing An MSI Package