Permissions to be granted to developers to investigate jobs, queries, and packages
2
votes
1
answer
221
views
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?


Asked by Marcello Miorelli
(17274 rep)
Aug 18, 2015, 11:43 AM
Last activity: Mar 25, 2024, 07:47 PM
Last activity: Mar 25, 2024, 07:47 PM