So... we have a legacy process in place which was developed by a third party some years ago and whilst it's no longer supported, it does still get executed by our team and provides decent results.
The process is developed in Microsoft Access. We have 2x members of staff who, twice a month, open this up and execute/start a process within Access. I'm not 100% sure what it does, but it keeps them happy!
Whilst I was doing an audit on one of our servers recently, I discovered that these 2x members of staff had SysAdmin access to the server. There didn't seem to be any logical reason as to why they'd have this level of access, so I immediately removed this. We later discovered this prevented them both from being able to execute/start the legacy process within Access.
After some digging, I discovered that when the legacy system is started, somehow it creates/adds a Schedule entry on a SQL Agent Job. The schedule gets created and is scheduled to run 2 minutes later.
I'm aware that because this process is amending a SQL Agent Job, that only the job owner (or sysadmin) can edit this - hence they now get a fail/error when trying to start the process.
Potential workarounds I've considered:
- Give the 2x users sysadmin indefinitely - not exactly an option we would consider.
- Run the process myself (as member of sysadmin) - not something I really want to be fussed with.
- Temporarily grant them sysadmin each time they request, then remove it immediately afterwards - not ideal either.
I know that if I changed the owner of the SQL Agent Job to one of the team members, then they'd be able to edit/amend the Job (as Job owner). But as there's 2 of them, it doesn't quite solve all problems.
Just wondering if anyone had any good ideas to get around this. Any help much appreciated