Workaround for this process without granting SysAdmin

SQL Server Administration
1

Background:
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.

Question:
Just wondering if anyone had any good ideas to get around this. Any help much appreciated :slight_smile:

2

:joy: always keep them happy, you will gain more allies when in the future you propose new things.

So question for you, what does the job do and if they went away on an extended vacation would the process stop - such as a report to admin?

3

It does some sort of data churn, then gives an end result. I haven't made much sense of it because it's all written in Visual Basic (which isn't my forte).

If these members of staff weren't available, it would be passed down the chain to others who would be expected to run it.

4

Is the little Access utility strictly for running dedicated for them to press a button or does it do more stuff?

5

Try adding the user to the SQLAgentOperatorRole in MSDB

1 Like
6

As noted, in the msdb db, give that user SQLAgentOperatorRole authority.

But rather than having them add a schedule, just have them start the job, via msdb.dbo.sp_start_job. The first step of the job can wait two minutes, using TSQL command:
WAITFOR DELAY '00:02:00'

I think you will run into permission failures trying to have a non-sysadmin user add a schedule to a job they don't own.