Job - změna vlastníka

EXEC msdb.dbo.spMeta_UpdateJobForNonAdmins
    @job_name = N'Jmeno_jobu',            -- zde zadat jmeno jobu
    @owner_login_name=N'DOMENA\username'  -- zde login noveho vlastnika
GO

Procedura

USE [msdb]

CREAT PROC [dbo].[spMeta_UpdateJobForNonAdmins]
      @job_id                       UNIQUEIDENTIFIER = NULL,
      @job_name                     sysname          = NULL,
      @new_name                     sysname          = NULL,
      @enabled                      TINYINT          = NULL,
      @description                  NVARCHAR(512)    = NULL,
      @start_step_id                INT              = NULL,
      @category_name                sysname          = NULL,
      @owner_login_name             sysname          = NULL,
      @notify_level_eventlog        INT              = NULL,
      @notify_level_email           INT              = NULL,
      @notify_level_netsend         INT              = NULL,
      @notify_level_page            INT              = NULL,
      @notify_email_operator_name   sysname          = NULL,
      @notify_netsend_operator_name sysname          = NULL,
      @notify_page_operator_name    sysname          = NULL,
      @delete_level                 INT              = NULL,
      @automatic_post               BIT              = 1
WITH EXECUTE AS OWNER
AS
BEGIN

    -- assign owner (or update) for job
   EXEC dbo.sp_update_job
      @job_id
      ,@job_name
      ,@new_name
      ,@enabled
      ,@description
      ,@start_step_id
      ,@category_name
      ,@owner_login_name
      ,@notify_level_eventlog
      ,@notify_level_email
      ,@notify_level_netsend
      ,@notify_level_page
      ,@notify_email_operator_name
      ,@notify_netsend_operator_name
      ,@notify_page_operator_name
      ,@delete_level
      ,@automatic_post


    -- assign owner for all schedule of job
    DECLARE @schedule_id BIGINT = 0
    -- Iterate over all schedule_ids
    WHILE (1 = 1)
    BEGIN

        -- Get next schedule_id
        SELECT TOP 1 @schedule_id = schedule_id
        FROM msdb.dbo.sysjobs S
            LEFT JOIN msdb.dbo.sysjobschedules SJ
                ON S.job_id = SJ.job_id
        WHERE S.name = @job_name
            AND schedule_id > @schedule_id
        ORDER BY schedule_id

        -- Exit loop if no more schedule_id available
        IF @@ROWCOUNT = 0 BREAK;

        PRINT @schedule_id

        EXEC sp_update_schedule @schedule_id = @schedule_id, @owner_login_name = @owner_login_name

    END
END