2

What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only? For example, I have 3 folders in the Integration Services catalog. Dept_A, Dept_B, and Dept_C. Each AD group should be able to DEPLOY to their own department's folder, and also create/schedule/run jobs that call these packages. I can control the permissions for deployment to those 3 folders by folder/project permissions. However, when it comes to the SQL Server Agent, things don't seem fine-grained enough. There are database roles in msdb: SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole. However, I'm not seeing how I can limit security to keep each AD group only able to run their own packages from a job.

0

I'm not seeing how I can limit security to keep each AD group only able to run their own packages from a job.

If users are in different AD groups and their visibility into the SSIS catalog is properly restricted, then you should be able to assume that only the appropriate SSIS packages will be selected when creating new jobs or modifying existing jobs. So what you really want to know comes down to your question of...

What permissions should I use for Active Directory groups to be able to deploy and then run/schedule their own jobs only?

Creation, scheduling, and starting/stopping of SQL Agent jobs are all actions that involve two things, namely the current owner of the job and the account of the user logged in regards to their privileges via those three roles you mentioned.

I would solve this by creating one login per department and update the "owner" of existing jobs to the login which corresponds to the department that is responsible for each respective job. Going forward, jobs are only assigned one of those logins.

For example, if each of these three logins belongs to the SQLAgentUserRole role, anyone using the login for Dept_A will only be able to see and modify jobs only owned by that login. Jobs owned by logins associated with Dept_B and Dept_C won't even be visible when utilizing the login for Dept_A. Credentials and SQL Agent proxies can then be setup in order to give any extra permissions to that login if needed (SSIS steps execute under the service account but any TSQL steps will use the job owner). I'm not completely sure if a credential and proxy can be setup to allow users to update jobs as their assigned login/owner, though.

Another option is to create stored procedures that wrap around the system-supplied stored procedures to conduct actions. Also see the social MSDN thread here. The downside with this is that I'm not sure how GUI-friendly this approach is and you'll need to create a wrapper for each stored procedure that does something (sp_update_job, sp_start_job). It will force all job changes to be scripted out which is a good practice in and of itself for things like review, troubleshooting, and documentation of changes but the trade-off is that more work happens for the developers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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