SQL Script that changes SQL Jobs Owner to SA for SQL Server 2000 and prior versions

Leave a comment (0) Go to comments

It’s not a good practice to use individual domain user login to run SQL Server Maintenance jobs.

The purpose of this script is to change the owner of every job on the server to run under the service id.  This will guarantee that all jobs will run use SA or specified login, regardless of who created them

SCRIPT to set SA as job owner for all jobs

-- ***********************************************************************************/
-- Description  :    Update job ownership to SA
--                    The purpose of this script is to change the owner of every job on the server to run under the service id.  This will guarantee that all jobs will run use SA or specified login, regardless of who created them
-- Compatibility  :     SQL 7.0,2000
-- This Script should be executed in MSDB database
-- ***********************************************************************************/
use msdb
go

declare @new_owner varchar(50)
set @new_owner = 'sa' -- you can a new USER ID, which you want to set as job owners

if not exists (select name from master.dbo.sysxlogins where name = @new_owner)
    begin
    print 'This is not a valid user in the sysxlogins table in the master database.  Job owner will not change.'
    return -1
    end

declare job cursor for
select name
from sysjobs
open job
declare @name_holder varchar(50)
fetch next from job into @name_holder
while (@@fetch_status = 0)
    begin
    exec sp_update_job 
        @job_name = @name_holder,
        @owner_login_name = @new_owner
    print 'Changed job_name ' + @name_holder + ' to run under the ' + @new_owner + ' account'
    fetch next from job into @name_holder
    end
close job
deallocate job

If you trying to update the Job owner for SQL Server 2005 and later versions, please check my other post


SQL Script that changes SQL Jobs Owner to SA

Tags : How to change the OWNER of a JOB using T-SQL,T-SQL – Change SQL Job Owners to SA,Change all SQL Job Owners to SA,SQL Script to Change all SQL Job Owners to SA,

EOF - SQL Script that changes SQL Jobs Owner to SA for SQL Server 2000 and prior versions, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.