Search

Allow for AD Groups to Execute a Job if Group is in proper MSDB SQL Agent Role by John Eisbrener

Closed

3
0
Sign in
 to vote
Type: Suggestion
ID: 770942
Opened: 11/13/2012 12:12:30 PM
Access Restriction: Public
0
Workaround(s)
Currently there is no easy way to allow for a Windows/AD Group to execute a job without elevating its rights to the SQLAgentOperatorRole or building a custom workaround.

I would like to allow any specified Windows/AD Group identical functionality as UserA outlined in the following situation:

UserA owns Job1 and is a member of the SQLAgentUserRole role. UserA can start/stop/modify/disable/enable Job1 at his/her leisure, but cannot create new jobs, delete jobs, or view/modify jobs he/she does not own.

I am requesting a way to allow for a Windows/AD Group to have the same functionality as UserA listed in the situation above. Currently I can do this with a SP and ownership chaining, but for the basic user that likes to use the UI, this is a rather cumbersome workaround.
Details (expand)

Product Language

English

Category

Tools (SSMS, Agent, Profiler, Migration, etc.)

Proposed Solution

Multiple ideas here as follows:

1) Allow a Windows Group to Own a Job (I understand this would probably break a lot of existing security ownership rules)
2) Create a new ownership object, "Group Owner", that allows you to assign a Group ownership of a job.
3) Revamp SQL Job security model to more closely emulate DB security model (e.g. Grant permissions to a user/group to Update/Edit/Delete/Run any/specific jobs currently hosted on an instance as opposed to using the MSDB JobAgent roles).

Primary Benefit

Improved Administration

Other Benefits

Set the product above it's peers in regards to the security model. Won't take much on this but ease of administration isn't pitched nearly as much as it should be when comparing SQL Server to it's RDBMS peers.

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 3/1/2013 at 11:59 AM
Hello

We took a look at this DCR along with several others. Unfortunately triaging it against other critical DCRs I do not think we would get to investigate and implement this in the near future, so we decided not to proceed with this DCR in the next SQL Server release.
However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this implemented.

Thanks for writing to Microsoft.
Alex Grach (MSFT)
Posted by Microsoft on 12/28/2012 at 9:48 AM
Hello

Thank you for proposing new DCR we always looking forward to improve our product based on the customer feedback.

We will consider this feature as a part of our planning for the next major release.

Thank you for your help
Alex Grach [MSFT]
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement