Permissions to be granted to developers to investigate jobs, queries, and packages

Question

I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them.

These servers have also SSAS and SSIS.

I have a group of developers there were helping with the connections and doing all the last developments on packages, and data warehousing stuff.

Now that everything is working, I want to remove the sysadmin permissions that I had previously granted them.

Now, these guys they have some requirements,

that I am willing to grant them, so that it is less weight on my shoulders:

1) They need to see query plans, 2) they need to identify missing indexes and other expensive operations like key lookups, etc

3) they need to query sys views and tables - monitor what procedures are running, collect WAIT STATS, etc

4) they also need to troubleshoot JOBS - so they need to be able to see them, see the job history, in order to find out what went wrong

5) they also need to see everything in the SSIS integration catalog, so that when something goes wrong inside one of there packages they are able to figure it out by themselves

I have granted them with the following SERVER permissions:

1) view server state

2) view any definition

on the MSDB database:

I got some ideas from "SQL Server Agent Fixed Database Roles"

and finally on the SSISDB database

Anything else that I should be aware of?

Answer 1

Don't grant ssis_admin if the dev shouldn't be sysadmins:

Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin.

Source: https://msdn.microsoft.com/en-us/library/ms141053.aspx

Personally I would never let the devs create jobs with their accounts. As soon as they leave the company and someone disables the AD account, all their jobs stop working...

Btw: don't grant server rights directly but with a serverrole.