0

Issue

Developers on the same team are members of a Windows_Group. These developers create jobs which they each own. Only the owner can edit the job so they cannot work together on same job. Because of this we end up with many versions of the same job.

Idea

Can I map the [Domain\Windows_Group] to a [Domain\Windows_User] so the jobs would need to be run and owned by the [Domain\Windows_User] account?

Sample code

What would I be missing or risking?

USE [master]
GO
CREATE LOGIN [DOMAIN\Windows_Group] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [msdb]
GO
CREATE USER [Domain\Windows_User] FOR LOGIN [DOMAIN\Windows_Group]
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [Domain\Windows_User]
GO

I would only do this in our development environment. Developers do not have db_owner or access to any security roles.

Testing

This will be used on a new server and is only a theory at the moment, so I have no developers to test yet.

0

1 Answer 1

1

When you add a user for the login (which is a group in AD/Windows) and somebody in that AD group creates a job, SQL will extract the SID for the AD user that created that job and records that sid as the owner of the job.

I.e., when somebody else in that AD group tries to modify the job it fails because somebody else owns that job.

You probably already know above, but just to make sure we are on she same page. Now, to your question:

"Can I map the [Domain\Windows_Group] to a [Domain\Windows_User] so the jobs would need to be run and owned by the [Domain\Windows_User] account?"

What you are asking doesn't make sense, sort of. If you re-read my above sentences you will understand why. The individual sid that created the job will still be the owner of the job.

What I have done is to create a stored procedure for my BI folks that allows them to change the owner of a job. I.e., we have the setup that I list above. So when somebody else in that AD group need to modify a job, that person must first take ownership of the job.

I recently rolled out this solution so time will tell if there are any unforeseen issues.

You need to understand security in Agent and SQL Server for this to work out. I.e., for non-TSQL job steps, your users will use Credentials/Agent proxies. And for T-SQL jobsteps, all involved parties need to understand that SQL does an EXECUTE AS LOGIN when execute the SQL you ask for (i.e., watch out for identity/permissions differences for T-SQL jobsteps).

Oh, and the proc that you develop for them to use should either use the EXECUTE AS 'dbo' attribute or use module signing - so it is allowed to change the owner of a job. And, finally, add appropriate safeguards in that proc. For instance I don't allow them to change the owner of a job to somebody that is sysadmin.

2
  • Do you grant access to the new sp only to the Windows_Group Commented May 13 at 18:24
  • Yes, only to those that need it, i.e. that windows group. Commented May 13 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.