none
Update Job owner for all SQL Agent jobs; RRS feed

  • Question

  • Hi  Is it possible to update the job owner for all sql agent jobs? I have several jobs on a server don't want to do it manually. I am running sql 2008 EE.

    Thanks

     

    Wednesday, May 11, 2011 3:13 PM

Answers

  • DECLARE @name_holder VARCHAR(1000)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT [name]  FROM msdb..sysjobs 
    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    exec msdb..sp_update_job
            @job_name = @name_holder,
            @owner_login_name = 'sa'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor
    **Changes the owner of the jobs to sa


    • Edited by aG_ Wednesday, May 11, 2011 3:48 PM .
    • Marked as answer by SQL_Jay Wednesday, May 11, 2011 5:48 PM
    Wednesday, May 11, 2011 3:46 PM
  • You can do this with Powershell.  I published a blog post that includes the necessary code example to do this:

    http://sqlskills.com/blogs/jonathan/post/Changing-SQL-Agent-Job-Owner-with-Powershell.aspx

     

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
    
    $InstanceName = "." 
    $NewOwnerLoginName = "sa"
    
    $smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName 
    $agent = $smosvr.JobServer;
    
    $agent.Jobs | % { 
      $_.set_OwnerLoginName($NewOwnerLoginName); 
      $_.Alter(); 
      }
    
    


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by SQL_Jay Wednesday, May 11, 2011 5:48 PM
    Wednesday, May 11, 2011 3:48 PM
    Moderator

All replies

  • USE

    [msdb]

    GO

    EXEC

    msdb.dbo.sp_update_job @job_name=N'BlockDetector', @owner_login_name=N

    'sa'

    GO

    This would be the code that you essentially need; however if you are wanting to peridically update a number of Jobs then you will need a robust process.

    One method would be to use a cursor.  Use the cursor to fetch in each job name into a variable; and then build and then build a string based on the job name and then execute the string.

    If it's a one-off job then run the following: -

    USE MSDB

    GO

    Select name from sysjobs

    Copy the results into notepad, put the string

    SELECT 'EXEC msdb.dbo.sp_update_job @job_name=N'

    before each listing, put the string

    ', @owner_login_name=N','sa'

    after each listing.  Copy the entire lot back into Management Studio and then execute the script.

     


    Please click "Mark As Answer" if my post helped. Tony C.
    Wednesday, May 11, 2011 3:44 PM
  • DECLARE @name_holder VARCHAR(1000)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT [name]  FROM msdb..sysjobs 
    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    exec msdb..sp_update_job
            @job_name = @name_holder,
            @owner_login_name = 'sa'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor
    **Changes the owner of the jobs to sa


    • Edited by aG_ Wednesday, May 11, 2011 3:48 PM .
    • Marked as answer by SQL_Jay Wednesday, May 11, 2011 5:48 PM
    Wednesday, May 11, 2011 3:46 PM
  • You can do this with Powershell.  I published a blog post that includes the necessary code example to do this:

    http://sqlskills.com/blogs/jonathan/post/Changing-SQL-Agent-Job-Owner-with-Powershell.aspx

     

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
    
    $InstanceName = "." 
    $NewOwnerLoginName = "sa"
    
    $smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName 
    $agent = $smosvr.JobServer;
    
    $agent.Jobs | % { 
      $_.set_OwnerLoginName($NewOwnerLoginName); 
      $_.Alter(); 
      }
    
    


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by SQL_Jay Wednesday, May 11, 2011 5:48 PM
    Wednesday, May 11, 2011 3:48 PM
    Moderator
  • Thanks Jonathan and others

    Wednesday, May 11, 2011 5:50 PM
  • you can exclude the jobs where the owner is not the SA instead updating all the jobs.

    DECLARE @name_holder VARCHAR(1000)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT j.name FROM msdb..sysjobs j INNER JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid where l.name <> 'sa'
    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    exec msdb..sp_update_job
            @job_name = @name_holder,
            @owner_login_name = 'sa'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

    Thursday, September 12, 2013 11:59 PM
  • a small correction.
    instead joining to the master.dbo.syslogins, i have used IN query as some of the sid might not match all the time.

    create proc dba_reset_job_owner_to_sa
    as

    DECLARE @name_holder VARCHAR(1000)
    DECLARE My_Cursor CURSOR
    FOR
    SELECT name FROM msdb..sysjobs where owner_sid not in (select sid from Master.dbo.syslogins where name = 'sa')

    --SELECT j.name FROM msdb..sysjobs j left outer JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid where l.name <> 'sa'

    OPEN My_Cursor
    FETCH NEXT FROM My_Cursor INTO @name_holder
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    exec msdb..sp_update_job
            @job_name = @name_holder,
            @owner_login_name = 'sa'
    FETCH NEXT FROM My_Cursor INTO @name_holder
    END 
    CLOSE My_Cursor
    DEALLOCATE My_Cursor


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

    Friday, September 13, 2013 12:04 AM
  • It might be better to slightly modify the T-SQL as shown below for fetching the jobs that do not have 'sa' as owner.

    SELECT name JobName, SUSER_SNAME(owner_sid) JobOwner FROM msdb..sysjobs where owner_sid in (SELECT owner_sid FROM msdb..sysjobs where  SUSER_SNAME(owner_sid) != 'sa')

    If the job is owned by a domain account and that account is not removed from ActiveDirectory but is removed from Server logins, the query as given in the post will not retrieve those jobs since it will not be in syslogins.

    Hence the slight change as above.

    Monday, January 25, 2016 7:08 PM
  • The nice thing about this script is it can be executed on multiple servers at once using CMS.   
    Saturday, October 1, 2016 4:08 PM