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?