SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

salt Release 1.1.2562 – With Bug Fix!


Very recently a user of salt got in touch with me about a bug when setting the job schedules up – when creating a schedule an owner is created. And only the owner (or sysadmin) can modify the schedule. So if the deployment account is not sysadmin then it needs to be the owner of the schedule.


An owner is identified by a SID, which is stored in a varbinary column in SQL Server, so when we set the SID variables in PowerShell they are stored as byte arrays. The issue here is that “–notmatch” doesn’t work as presumably expected in that case as shown below.

View the code on Gist.


What we have to do is to change it to something like….

View the code on Gist.

What we’re doing here is checking to see if there is any output when running a “Compare-Object” between the two SIDs. If there is then we cannot alter the schedule.  So only one line needed to be changed. Phew!

Additional Notes

This SID check only occurs when a user is NOT a sysadmin. So if your accounts have sysadmin in some environments but not others you’d see this occur only in those environments where the account running the task is NOT a sysadmin.

It’s also worth noting that this path is only taken when a schedule does not exist in the XML but does in the server – so as the behaviour here is to drop the schedule the task has to verify that if it is not a sysadmin then it is the owner of the schedule. Simples!


Richie Lee's SQL Stuff

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.


Leave a comment on the original post [bzzzt.io, opens in a new window]

Loading comments...