Thursday, September 11, 2014

Practical Privileges to SSIS Reports in Production

Update 3/30/2016: This apparently doesn't work in SQL Server 2014. SQL Server 2016 is supposed to provide a proper fix for SSIS permissions so I probably won't monkey around with it any further.

SSIS Catalog Security in SQL Server 2012: one of those cases where Microsoft could have hit a home run but only made it to third base. The one thing that held them back was the reporting permissions. Basically, you can only see reports for package executions that you kicked off unless you're a member of the "ssis_admin" role.

Just think about this in Production for a minute. You're a developer and you can't see reports from executions that were started by your scheduling tool. Yeah, the ones you're supposed to be troubleshooting when they go wrong. Since it's a Production server you aren't allowed to run jobs (I hope) so the reports will never return any data for you. So, why don't we have a "ssis_all_reports" role or something that I can use to grant developers access to all the reports? Alas, this didn't make the cut and Moni Sinha's Connect issue was closed as "Won't Fix."

Well, there is a workaround but it's a bit of a hack (even if it is easy as pie to implement). It starts by making your developers members of the "ssis_admin" role. Seriously, keep reading. You don't think I would just give them blanket access like that do you?

Step two is your safety net. It's as simple as adding the developers to the "db_denydatawriter" role as well. That keeps them from starting packages or deploying stuff to your server.

This works best if all your developers are in the same Active Directory security group. Just add that group to both roles and you're done. It would be bad if you added an individual account to the "ssis_admin" role but forgot to put them in "db_denydatawriter"!

Thank you Rachel Van den Berg for your comment in the article Managing SSIS Security with Database Roles. Your solution is just what we need until Microsoft can give us a proper solution.