Change Job owner for all SQL Server Agent Jobs - SQL Server Scripts

I was checking my emails and saw below error message from SQL Server monitoring tool.
"The job failed.  The owner '' of job '' does not have server access.",

That means a DBA had created a SQL Server Agent  job with his name as owner. Once he/she will leave the company and account will be removed from AD, the jobs will start failing.

It is good idea to use service account or sa as SQL Server Job owner, so you don't have to worry in case owner leave the company:)


Below Query will return you Job names and their owners.

SELECT J.NAME AS JobName
    ,L.NAME AS OwnerName
FROM msdb..sysjobs J
LEFT JOIN master.sys.syslogins L ON J.owner_sid = L.sid


Now if we would like to update all the job where owner is not sa, we can use below query. You can also modify the query to filter the jobs for which you would like to update owner.

--Provide the New Owner for The Jobs you like to Change, I am using sa
DECLARE @NewOWNERName VARCHAR(100)

SET @NewOWNERName = 'sa'

DECLARE @OldJobOwner VARCHAR(100)
DECLARE @JobName VARCHAR(1000)

DECLARE Job_Cursor CURSOR
FOR
--Change your Query as per requirements, I am selecting all Job where owner<>sa
SELECT J.NAME AS JobName
    ,L.NAME AS OwnerName
FROM msdb..sysjobs J
LEFT JOIN master.sys.syslogins L ON J.owner_sid = L.sid
WHERE L.NAME <> @NewOWNERName

OPEN Job_Cursor

FETCH NEXT
FROM Job_Cursor
INTO @JobName
    ,@OldJobOwner

WHILE (@@FETCH_STATUS <> - 1)
BEGIN
    EXEC msdb..sp_update_job @job_name = @JobName
        ,@owner_login_name = @NewOWNERName

    PRINT 'Ownerd Change for ' + @JobName + 'Job from ' + @OldJobOwner + ' to '
 + @NewOWNERName

    FETCH NEXT
    FROM Job_Cursor
    INTO @JobName
        ,@OldJobOwner
END

CLOSE Job_Cursor

DEALLOCATE Job_Cursor


Just to be on safe side, test the script in Development environment before run in production. 

44 comments:

  1. There is something liberating and thrilling about knowing that any casino game that you could possibly want to play is available at your fingertips.

    sbobet88 mobile

    ReplyDelete
  2. The company web site is rapidly becoming the first point of contact for most job seekers or prospective clients. Contractors should update their corporate web site so that it provides a professional and interactive presentation of the firm, its goals, key personnel, corporate culture, top achievements, and business philosophy.Cover letter

    ReplyDelete
  3. Establish a Timeline: Your goals establish what you want to do with your career and how you can develop it through incremental steps. Your priorities determine the immediacy of your goals. For example, ashamed of being unemployed a goal and top priority may be finding a job immediately.

    ReplyDelete
  4. I adore your websites way of raising the awareness on your readers.fake packwoods

    ReplyDelete

  5. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    먹튀사이트검증

    ReplyDelete
  6. It is perfect time to make some plans for the future and it is time to be happy. I've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it! For more visit here construction employment recruiters in USA

    ReplyDelete
  7. I’ve been absent for a while, but now I remember why I used to love this website. Thank you, I will try and check back more often. How frequently you update your site? Pharmacist Jobs in Government

    ReplyDelete
  8. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work! romani in uk

    ReplyDelete
  9. If we had to relate this to a job description, then it would probably entail the production of goods in a manner that is more efficient than it would have been produced under traditional means. If this is the case then I propose this basic definition: California environmental jobs

    ReplyDelete
  10. The fifth factor that can produce peak performance in sports is actually finding the right players to fill up the most key positions of the team first. In any sport teams, there will be key positions that must be filled first. portable dartboard stand

    ReplyDelete
  11. Thank you very much for writing such an interesting article on this topic. This has really made me think and I hope to read more. ランディングページ制作

    ReplyDelete
  12. It is great to have the opportunity to read a good quality article with useful information on topics that plenty are interested one.I concur with your conclusions and will eagerly look forward to your future updates. need job fast

    ReplyDelete
  13. No doubt Social Media Networking - Facebook, Twitter, LinkedIn and sources like DIGG are powerful agents, however one must realize that they are informational/promotional platforms and vehicles - in themselves, they are not a Marketing solution. Salesforce interview questions and answers

    ReplyDelete
  14. Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here. job bank

    ReplyDelete
  15. Cauti locuri de munca in Romania sau Strainatate? Intra acum pe MyNextJob. La noi gasesti oferte de angajare si joburi care ti se potrivesc! anunturi munca

    ReplyDelete
  16. This is my first time visit to your blog and I am very interested in the articles that you serve. Provide enough knowledge for me. Thank you for sharing useful and don't forget, keep sharing useful info: aesthetic injection courses

    ReplyDelete
  17. You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. jobs

    ReplyDelete
  18. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. visit yourvirtualofficelondon.co.uk

    ReplyDelete
  19. Your site is truly cool and this is an extraordinary moving article. Enewresult

    ReplyDelete
  20. it was a wonderful chance to visit this kind of site and I am happy to know. thank you so much for giving us a chance to have this opportunity.. VON

    ReplyDelete
  21. Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing. Lead Auditor training

    ReplyDelete
  22. Merely a smiling visitant here to share the love (:, btw outstanding style. ankara yabancı dil kursu

    ReplyDelete
  23. Try not to mix web-based media and your online expert picture. It is critical to keep your hidden life PRIVATE. That is a mix-up numerous individuals make with individual marking which may cost them being chosen for that next job or opportunity. Free Resume Builder

    ReplyDelete
  24. I have read all the comments and suggestions posted by the visitors for this article are very fine,We will wait for your next article so only.Thanks! Job

    ReplyDelete
  25. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. Job

    ReplyDelete
  26. Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! 무료릴게임

    ReplyDelete
  27. The arrangement is to follow a basic interaction to terminating that is successful, caring, and reasonable.Europa-Road targonca szállítás Debrecen

    ReplyDelete
  28. Someone Sometimes with visits your blog regularly and recommended it in my experience to read as well. The way of writing is excellent and also the content is top-notch. Thanks for that insight you provide the readers! textbook answers

    ReplyDelete
  29. The manner may be complex and tedious, that's why hiring a incapacity legal professional may be a treasured asset.Broker Advisor

    ReplyDelete
  30. Thanks for sharing the post.. parents are worlds best person in each lives of individual..they need or must succeed to sustain needs of the family. Obchodní-vízum-Indie

    ReplyDelete
  31. I read that Post and got it fine and informative. 먹튀사이트

    ReplyDelete
  32. I am thankful to you for sharing this plethora of useful information. I found this resource utmost beneficial for me. Thanks a lot for hard work. 10017 lake ave

    ReplyDelete
  33. Excellent and very exciting site. Love to watch. Keep Rocking. บาคาร่า

    ReplyDelete
  34. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. 먹튀검증

    ReplyDelete
  35. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. Διαδικτυακή αίτηση βίζας για τον Καναδά

    ReplyDelete
  36. Great article by the great author, it is very massive and informative but still preaches the way it sounds like that it has some beautiful thoughts described so I really appreciate this article. Industrial Staffing Agency in USA

    ReplyDelete
  37. At this time, if you can use good medicinal materials and ingredients to strengthen hormones,Staffing Agency Paterson Nj will be able to retain your youth and enjoy masculine power.

    ReplyDelete
  38. I am glad you take pride in what you write. This makes you stand way out from many other writers that push poorly written content. 토토사이트

    ReplyDelete
  39. Thanks for sharing this quality information with us. I really enjoyed reading. Will surely going to share this URL with my friends. jobs recruiting

    ReplyDelete
  40. The information in the post you posted here is useful because it contains some of the best information available. information technology staffing agency USA. Thanks for sharing it. Keep up the good work.

    ReplyDelete
  41. This comment has been removed by the author.

    ReplyDelete
  42. I am looking for and I love to post a comment that "The content of your
    post is awesome" Great work!
    fuel injectors online

    ReplyDelete