Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.428 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • October 14th, 2018
  • 3
  • Database Maintenance SQL Server SQL Server Agent

SQL Server - How to change the owner of all jobs for a user in SQL Agent

Views: 1.211
Reading Time: 3 minutes

Hello everybody!
Let's go for another article.

Introduction

DBAs in environments where the bank's BI developers and analysts are not sysadmin, but create jobs in the bank using their own users (SQL Server - Understanding SQL Agent Permissions and Roles (SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole)) knows how common job owner change demands are, especially when a user leaves the company and their jobs need to be transferred to someone else's responsibility.

From this scenario, I would like to share with you how to change the owner (s) of a SQL Agent job in a practical, simple and quick way for both a specific job and all jobs of a user.

How to identify the owner of SQL Server Agent jobs

To begin this article, I will briefly demonstrate how to identify the owner of SQL Server Agent jobs using a simple T-SQL query, where you can list all instance jobs and their respective owners:

Transact-SQL
1
2
3
4
5
6
7
8
SELECT
    A.job_id,
    A.[name] AS job_name,
    B.[name] AS [user_name],
    B.[sid]
FROM
    msdb.dbo.sysjobs A
    LEFT JOIN sys.server_principals B ON A.owner_sid = B.[sid]

Example:

How to change the owner of a job with SSMS or Azure Data Studio

The simplest and easiest way to change the owner of a job is by using the SQL Server Management Studio (SSMS) interface.

Or, use the Azure Data Studio interface (aka SQL Operations Studio)

How to change the owner of a job with sp_update_job

The simplest way to change the owner of a job using T-SQL scripts is through the system stored procedure, msdb.dbo.sp_update_job, which allows you to change various settings for a given job, including the job owner.

Changing the owner of a job from job name

Transact-SQL
1
2
3
EXEC msdb.dbo.sp_update_job
    @job_name = 'Teste de Job', -- sysname
    @owner_login_name = 'sa' -- sysname

Changing the owner of a job from job ID

Transact-SQL
1
2
3
EXEC msdb.dbo.sp_update_job
    @job_id = 'FEC4CB2F-A39D-4FCA-8594-9388348C5C65', -- sysname
    @owner_login_name = 'sa' -- sysname

How to transfer jobs between logins with sp_update_job

When you need to transfer ownership of jobs from one user to another, this task can end up being a bit cumbersome. To assist with this task, I have prepared a script to perform this operation for you.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
    @CmdUpdateJob VARCHAR(MAX) = '',
    @LoginOrigem VARCHAR(100) = 'dirceu.resende',
    @LoginDestino VARCHAR(100) = 'sa'
 
 
SELECT @CmdUpdateJob += '
EXEC msdb.dbo.sp_update_job @job_id = ''' + CAST(A.job_id AS VARCHAR(50)) + ''', @owner_login_name = ''' + @LoginDestino + ''';'
FROM
    msdb.dbo.sysjobs A
    JOIN sys.server_principals B ON A.owner_sid = B.[sid]
WHERE
    B.[name] = @LoginOrigem
 
 
EXEC(@CmdUpdateJob)

Example of script execution:

How to transfer jobs between logins with sp_manage_jobs_by_login

As we saw above, in a scenario where it is necessary to transfer logins from one user to another, we can use the script above to do this manually using the sp_update_job stored procedure. However, from SQL Server 2008 we can use another system sp, the sp_manage_jobs_by_login, which allows you to transfer jobs from one owner to another with just one execution.

Transact-SQL
1
2
3
4
EXEC msdb.dbo.sp_manage_jobs_by_login
    @action = N'REASSIGN',  
    @current_owner_login_name = N'dirceu.resende',  
    @new_owner_login_name = N'sa';

Execution Result:

How to change the owner of all jobs

And finally, if you want to change the owner of all jobs in your instance, you can manually use sp_update_job to apply this change to your environment with the script below:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
    @CmdUpdateJob VARCHAR(MAX) = '',
    @LoginDestino VARCHAR(100) = 'sa'
 
 
SELECT @CmdUpdateJob += '
EXEC msdb.dbo.sp_update_job @job_id = ''' + CAST(A.job_id AS VARCHAR(50)) + ''', @owner_login_name = ''' + @LoginDestino + ''';'
FROM
    msdb.dbo.sysjobs A
 
 
EXEC(@CmdUpdateJob)

Well guys, I hope you enjoyed this article and that it is useful to you on a daily basis.
A big hug and until next time!

tags: sp_manage_jobs_by_loginsp_update_jobsqlsql agentsql serversql server agent

You may also like ...

  • CPF and CNPJ generator valid for SQL Server environment testing

  • SQL Server - How to concatenate rows by grouping data by a column (Grouped concatenation)

  • SQL Server - TCP Dynamic Ports vs TCP Port (Dynamic vs. Static Port)

  • Next How was the 1st SQL Server Meetup at Microsoft - Slides and Material
  • Previous SQL Server - How to identify data conversion errors using TRY_CAST, TRY_CONVERT, TRY_PARSE, ISNUMERIC, and ISDATE

Comments

  • Comments3
  • Pingbacks0
  1. Thiago said:
    December 2 from 2018 to 11: 20

    The best site to learn advanced SQL Server features!

    Reply
  2. Renato Castro said:
    17 October 2018 to 09: 38

    Very good!

    Reply
  3. Sandro said:
    15 October 2018 to 10: 32

    Cool

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.