Hippolito

Untitled

Aug 14th, 2019
89
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.92 KB | None | 0 0
  1. USE [msdb]
  2. GO
  3.  
  4. DECLARE @owner_login_name AS NVARCHAR(100)
  5. SET     @owner_login_name = ORIGINAL_LOGIN()
  6.  
  7. SELECT
  8.        'EXEC msdb.dbo.sp_update_job @job_id = N''' + CAST(a.job_id AS NVARCHAR(MAX)) + ''', @owner_login_name = ''' + @owner_login_name + ''';'    --> changeJobOwner
  9.       ,'EXEC dbo.sp_update_schedule @schedule_id = ''' + CAST(a.schedule_id AS NVARCHAR(MAX)) + ''', @owner_login_name = ''' + @owner_login_name + ''';'  --> changeShedOwner
  10.     --, a.job_id 'jobId'
  11.       ,a.schedule_id AS 'shedId'
  12.       ,b.[name]      AS 'jobName'
  13.       ,c.[name]      AS 'shedName'
  14.       ,e.[name]      AS 'jobOwner'
  15.       ,d.[name]      AS 'shedOwnerName'  
  16.  
  17. FROM [dbo].[sysjobschedules]    a
  18.      JOIN [dbo].[sysjobs]       b ON a.job_id      = b.job_id
  19. LEFT JOIN [dbo].[sysschedules]  c ON a.schedule_id = c.schedule_id
  20. LEFT JOIN sys.syslogins         d ON c.owner_sid   = d.sid
  21. LEFT JOIN sys.syslogins         e ON b.owner_sid   = e.sid
Add Comment
Please, Sign In to add comment