So in this post I showed you a technique for updating the owner on all your SQL Agent jobs.
Whilst this isn’t a major change, you do need to have checked that the new ownership account has the correct permissions or some thing (eg; EXECUTE AS
or CMDEXEC
steps). With this in mind someone asked me how I’d back this out. Either for all jobs or that one specific MUST NOT FAIL job that is failing.
As we have 2 ways to change them, we have 2 ways to back out the changes. Note that both of these require you to have done a small amount of work up front, there is no magic rollback option 🙁
PowerShell and dbatools
This one is a little simpler as we can use a lot of PowerShell plumbing to do the lifting for us. To set this up we use:
Get-DbaAgentJob -SqlInstance server1 | Select-Object Name, OwnerLoginName | ConvertTo-Csv | Out-File .\server1-JobOwners.csv
If you’re not a PowerShell user I’ll just break this down into steps:
- We user dbatools’ `Get-DbaAgentJob` cmdlet to get all the Agent jobs on Server1
- We pipe the ouput to `Select-Object`, we select just the job Name and it’s Owner’s login name
- The output is then converted to a CSV object
- Finally we write the date to a file in the current folder
So we’ve created a nice csv list of the original owners of our jobs, great. Now how do we use those to roll back?
To roll back every job:
Get-Content .\server1-JobOwners.csv | ConvertFrom-Csv | Foreach-Object {Set-DbaAgentJob -SqlInstance Server1 -Job $_.Name -OwnerLoginName $_.OwnerLoginName}
To rollback just one job it’s very similar, we just insert a Where-Object
step into the pipeline:
Get-Content .\server1-JobOwners.csv | ConvertFrom-Csv | Where-Object -Name -eq 'My Job' | Foreach-Object {Set-DbaAgentJob -SqlInstance Server1 -Job $_.Name -OwnerLoginName $_.OwnerLoginName}
In both cases we read in our CSV, convert it from CSV into a PowerShell Object. For a single job we use Where-Object
to filter down to that one job, you could also use like
if you wanted to pattern match. The remain records then get piped through a ForEach-Object
loop, where we use Set-DbaAgentJob
to reset the jobs owner.
T-SQL
The pre setup for this one involves creating and populating a table to store the old values:
CREATE TABLE JobOwnerHistory( Job_Id char(36), JobName nvarchar(128), OwnerLoginName nvarchar(50) ) INSERT INTO JobOwnerHistory SELECT sj.job_id, sj.JobName, sp.name as 'OwnerLoginName' FROM msdb..sysjobs sj INNER JOIN sys.server_principals sp on sj.owner_sid=sp.sid
So now, resetting a job’s owner is just a modification of our original script:
DECLARE @job_id char(36) DECLARE @OwnerLoginName varchar(50) DECLARE JobCursor CURSOR FOR SELECT job_id, OwnerLoginName FROM JobOwnerHistory --WHERE JobName LIKE '*Finance*' OPEN JobCursor FETCH NEXT FROM JobCursor INTO @job_id, @OwnerLoginName WHILE (@@FETCH_STATUS <> -1) BEGIN exec msdb..sp_update_job @job_name = @job_id, @owner_login_name = @OwnerLoginName FETCH NEXT FROM JobCursor INTO @job_id, @OwnerLoginName END CLOSE JobCursor DEALLOCATE JobCursor
As written that will reset every job in JobOwnerHistory
, if you want to filter down to a subset of tables you’d uncomment and modify the WHERE
line
Hope those examples are helpful. If you’ve stumbled across this page and it doesn’t quite fix your problem, please drop a comment and I’ll try to lend a hand.
Leave a Reply