LearnItFirst User Forum

/*
    Author: Scott Whigham FROM http://www.LearnSqlServer.com/

    Description: This script changes ownership of all jobs to 'sa' and also changes all jobs to notify an operator ("Scott") on complete

assigns the 'sa' account to all jobs and notifies an operator 'Scott' on completion

    Versions: SQL Server 2005 and should work for 2000 but have not tested

    Creation Date: May 2, 2007

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
SET NOCOUNT ON
DECLARE @Jobs TABLE (job_id UNIQUEIDENTIFIER)
INSERT @Jobs (job_id)
    SELECT job_id FROM msdb.dbo.sysjobs

DECLARE @job_id UNIQUEIDENTIFIER
SELECT TOP 1 @job_id=job_id FROM @Jobs

WHILE @job_id IS NOT NULL
    BEGIN
        EXEC msdb.dbo.sp_update_job @job_id=@job_id,
                @notify_level_email=3,
                @notify_level_netsend=2,
                @notify_level_page=2,
                @notify_email_operator_name=N'Scott', -- change with your operator name
                @owner_login_name=N'sa' -- the login to change the ownership
        
        DELETE @Jobs WHERE job_id=@job_id
        SET @job_id = NULL
        SELECT TOP 1 @job_id=job_id FROM @Jobs
    END
GO