Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [msdb]
- GO
- DECLARE @owner_login_name AS NVARCHAR(100)
- SET @owner_login_name = ORIGINAL_LOGIN()
- SELECT
- 'EXEC msdb.dbo.sp_update_job @job_id = N''' + CAST(a.job_id AS NVARCHAR(MAX)) + ''', @owner_login_name = ''' + @owner_login_name + ''';' --> changeJobOwner
- ,'EXEC dbo.sp_update_schedule @schedule_id = ''' + CAST(a.schedule_id AS NVARCHAR(MAX)) + ''', @owner_login_name = ''' + @owner_login_name + ''';' --> changeShedOwner
- --, a.job_id 'jobId'
- ,a.schedule_id AS 'shedId'
- ,b.[name] AS 'jobName'
- ,c.[name] AS 'shedName'
- ,e.[name] AS 'jobOwner'
- ,d.[name] AS 'shedOwnerName'
- FROM [dbo].[sysjobschedules] a
- JOIN [dbo].[sysjobs] b ON a.job_id = b.job_id
- LEFT JOIN [dbo].[sysschedules] c ON a.schedule_id = c.schedule_id
- LEFT JOIN sys.syslogins d ON c.owner_sid = d.sid
- LEFT JOIN sys.syslogins e ON b.owner_sid = e.sid
Add Comment
Please, Sign In to add comment