Sql-server – SQL Server – SQL Agent – Allow a Group to own a job

permissionssql-server-2008-r2sql-server-2012sql-server-agent

Scenario

There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move away from this. Everyone is accessing the database though their own login where each person has access to their groups systems. So Support has access to support functions and jobs etc.

The allowed permission for the procedures and tables are really easy. Create a login from the windows group. Then create an user based on that login and allow it to read and execute on the new schema that we're creating for them.

We are running SQL Server 2012 and 2008 R2.

Problem

The SQL Agent Jobs sits above the user level and requires a login to be assigned to the owner. But it doesn't take a group login as an accepted parameter:

The specified @owner_login_name is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

The hope is to allow the groups to have their own set of jobs that they can manipulate directly (on a reporting server not the live database). Without effecting others departments work or the other jobs (DB maintenance tasks, replication etc). But allowing other members of a team to manipulate a job if the original writer is out of the office or has left.

I've tried searching around but so far the only thing I've found on sqlservercentral.com from back in 2010 saying it can't be done.

Things I'm looking at

Having the SQL Agent job assigned to a support / devopment generic account that nobody has access to. But everyone somehow has access to manipulate their jobs.

This would allow people to alter the group's jobs while allowing jobs to continue being used after an individual leaves the company.

I've not found a way for people to be able to access / run jobs by that have other owners (even when they are using the same windows group login to sql). Without granting them SQLAgentOperateorRole (which would defeat the point in locking them out) of each other's (and the admin's) jobs.

Create a user per group that has the added access of the msdb rights along with the standard support abilities that they can log into to manipulate jobs.

The disadvantage of this is now the users have two logins one of which won't change. So an user leaving the company would still have the login credentials for that account potentially, it would also lead to users being lazy and only ever logging in under that account. (They shouldn't but let's be honest, they will).

Having a SQL job that runs every 5 minutes, each group has a table with commands for run this command at frequency x (essentially one SQL agent job runs a custom written SQL Agent list of jobs).

I don't think I need to go through how horrible an idea this is. But it's something that has semi been considered.

Overview

Obviously the best solution that we can see for us, would be to allow a group to own a SQL Agent job but it doesn't seem like this is possible.

Anyone have any ideas? For now guidance on how to implement something like this would be appreciated.

Best Answer

Since SQL Server Agent does not support using a Windows Group as the owner of a SQL Server Agent job. You can do it, but it means an end run around the built in restrictions.

You can use some code that is posted at the link below.

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This approach does an 'end run' around the "owner" of the SQL Agent job for all practical purposes of execution. This is because the dbo.StartAgentJob stored procedure uses the Logins and Groups in the dbo.msdbJobMap table to decide who can run a SQL Agent Job. As written, it will include any member of a group assigned to the job, has the rights to start the job.

This means that anyone with rights to create a SQL Server Agent Job (a member of SQLAgentReaderRole or higher) can create and modify their own josb.

If they also have rights to insert the proper GroupName and JobName in the dbo.msdbJobMap table, anybody in those groups assigned to the job can execute the job as well.

Although a GROUP does not own the job directly, the dbo.StartAgentJobstored procedure will use its elevated permissions to determine whether to allow the Login and/or members of a Group to execute this job.

What still remains as a problem?

The owner of the SQL Agent Job is the only one who can edit the job contents. (Of course, a sysadmin can do almost anything.)

If the SQL Agent job needs to be rewritten and the owner no longer exists in your system, the sysadmin can change the job owner from the defunct account to the new designated owner. (Or script the job out and give the script to the developer who will do the rewrite and give the new job a new name.)

So, it is not a 100% solution to make the developers completely independent from the administrators, but it should Greatly Reduce the interactions needed.

Footnote: If the job is to run on distributed servers this is not enough.

Related Question