Change and Rename SQL Server Agent Job Name and Owner Login Name

On many occasions you need to change the SQL Server Agent Job Name and Login Name in your environment. Below script will change the job name and set the login name to ‘sa’.

DECLARE @servername NVARCHAR(30)=@@SERVERNAME,

        @name VARCHAR(100),

        @jobname NVARCHAR(130),

              @script NVARCHAR(300)

SET NOCOUNT ON;

DECLARE Job_Cursor CURSOR FOR 

SELECT name FROM msdb..sysjobs 

WHERE name LIKE 'DB_Maintenance%' AND name NOT LIKE '%'+@servername+'%'

OPEN Job_Cursor; 

FETCH NEXT FROM Job_Cursor INTO @name

WHILE @@FETCH_STATUS = 0 

   BEGIN 

   SET @jobname=@name+'_'+@servername

   SELECT @script='EXEC msdb..sp_update_job         @job_name='''+@name+''''+',@new_name='''+@jobname+''''+',@owner_login_name=''sa'''

   PRINT @script

   EXEC sp_executesql @script

   FETCH NEXT FROM Job_Cursor INTO @name; 

   END; 

CLOSE Job_Cursor; 

DEALLOCATE Job_Cursor; 

GO