October 10, 2011 at 11:57 am
I have a requirement for a database environment in which the developers can perform the following:
1. Users in an AD group can add/remove databases they own and everyone in the group become db_owner automatically.
2. Users in an AD group can add sql jobs.
3. Users in an AD group can modify/execute other users jobs but not the ones created by sysadmin (DBA).
As far as requirement #1 this is easy for an individual login but not a group. The best way I can think to accomplish this is allow the group "Grant Create Any Database" and grant the group db_owner on the model database (YUCK!). Can anyone think of a better way to do this that doesn't require DBO access to the model database?
#2 seems fairly easy by adding the group to the SQLAgentOperatorRole in MSDB. However #3 is proving to be much more difficult. The login that creates the job does have rights to the job and it has view only rights to other jobs. How do I grant userA modification rights to userB's jobs when they are in the same group?
Thanks in advance for the help!
JLC
October 10, 2011 at 1:00 pm
#1 I would provide them a script\stored proc to create all databases which includes the permissions.
#3 The ONLY way to accomplish this (short of granting sysadmin) is to create a SQL authenticated id which they must log on with when manipulating jobs.
---------------------------------------------------------------------
October 10, 2011 at 1:16 pm
george sibbald (10/10/2011)
#1 I would provide them a script\stored proc to create all databases which includes the permissions.
That makes sense. I thought about going that route too.
george sibbald (10/10/2011)
#3 The ONLY way to accomplish this (short of granting sysadmin) is to create a SQL authenticated id which they must log on with when manipulating jobs.
Is this because of the requirement not to be able to see DBA jobs? If that didn't exist would the SQL Authenticated account be preferred over say giving the users TargetServerRole/SQLAgentOperatorRole and a custom version of sp_start_jobs that they have access to?
October 10, 2011 at 1:46 pm
I think it is a security issue, not allowing users to execute other peoples jobs and thus granting them access they should not have.
I dare say the devs would prefer to be able to use their normal windows ids but I have not gone the sp_start_job route so cannot say what other issues that may lead to and I personally would not amend system stored procedures, it could bite you at upgrade times for instance.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply