SQL Script that changes SQL Jobs Owner to SA

Leave a comment (1) Go to comments

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

SQL Server Maintenance  Wizards has a limitation. Whenever someone check / update SQL Job, SQL Server automatically change the ownership to user domain account. You need to to manually change the ownership back to SA.

If you have the 5-7 jobs on Server and you need to do it on multiple servers, then it’s going to take some time.

A quick workaround to this is to use the following script, which will identify the non SA jobs and will change their owner to SA

SCRIPT to set SA as job owner for all jobs

-- ***********************************************************************************/ -- Description : Update job ownership to SA -- Script that change the Jobs Owner to SA. This Script will identify the non SA jobs and will change their owner as SA -- Compatibility : 2005 +

-- This Script should be executed in MSDB database -- ***********************************************************************************/ use msdb

go

DECLARE @db_job_count INT SELECT @db_job_count = count(distinct(suser_sname(owner_sid))) from msdb..sysjobs where suser_sname(owner_sid) <> 'sa' IF @db_job_count > '0' BEGIN DECLARE @change_job_id VARCHAR(50) DECLARE @change_job_name VARCHAR(100) DECLARE job_id_cursor CURSOR FOR SELECT job_id, name FROM msdb..sysjobs WHERE suser_sname(owner_sid) <> 'sa' OPEN job_id_cursor FETCH NEXT FROM job_id_cursor INTO @change_job_id, @change_job_name WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sql_statement NVARCHAR(255) EXEC msdb..sp_update_job @job_id = @change_job_id, @owner_login_name ='sa' PRINT 'Job ' + @change_job_name + ' has been updated to sa ownership' FETCH NEXT FROM job_id_cursor INTO @change_job_id, @change_job_name END CLOSE job_id_cursor DEALLOCATE job_id_cursor END


OUTPUT

SQL Script that changes SQL Jobs Owner to SA T SQL   Change SQL Job Owners to SA SQL Script to Change all SQL Job Owners to SA How to change the OWNER of a JOB using T SQL Change all SQL Job Owners 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, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. Good script, but you don’t need cursor, try this :

    DECLARE @Sql VARCHAR(MAX)

    SET @Sql = (
    SELECT ‘EXEC msdb..sp_update_job @job_id = ”’ + CAST(job_id AS VARCHAR(50)) + ”’, @owner_login_name =”sa”’
    FROM msdb..sysjobs WHERE suser_sname(owner_sid) ‘sa’
    FOR XML PATH(”)
    )
    PRINT @Sql
    EXEC (@Sql)

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.