Thursday, September 11, 2014

Practical Privileges to SSIS Reports in Production

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.

Monday, June 3, 2013

Reports are Dumb

Really, reports are dumb. This is not to say that people that use them are dumb, not at all. This is really about the fact that reports, as we have come to know them, don't do much for us or our business partners.We can make some seriously pretty data output with Crystal Reports, Business Objects Webi, or SQL Server Report Builder (well, maybe not that last one) that answer key business questions. That sounds important, and it is, but the problem is that the usefulness of the report ends with the answer.

Thursday, May 30, 2013

sp_Blitz Features for BI Admins

I recently discovered the sp_BLITZ™ – SQL Server Takeover Script via the SQL Server Central Daily Newsletter. (You need to sign up for this if you don't already.) You can find sp_BLITZ™ at http://www.brentozar.com/blitz/. I took a quick look at it and found several features that would be very useful in a SQL Server BI environment.

Friday, May 24, 2013

Request Addition of Code Analysis Extensibility to SSDT DB Projects

I really like the SSDT database projects and I have recently convinced our BI team to start using them to maintain our smaller databases. I am working on creating a database projects for our Staging and Data Warehouse projects and have run into a shortcoming in the SSDT DB Project capabilities.

Friday, September 28, 2012

Weekly TidBIts (September 28, 2012)

I don't have a lot of experience doing anything very advanced in Excel so I ended up researching some stuff related to Turtle Charts this week. As a result, two Excel articles kick off the Weekly TidBIts today. In fact, despite a busy week, I don't have a lot of SQL Server or MSBI stuff for you until the very last item. It's all good stuff though so check it out if you have time.

Friday, September 21, 2012

Weekly TidBIts (September 21, 2012)

Whoa! It's been a while but I have some neat stuff to share today. SSIS and SSAS/MDX are in the mix along with a special treat if you're "of age."


SQL Server Upgrade Gotcha: Read-only Databases

Let me start off by saying that this is not Microsoft's fault. You really can't expect any different behavior in this scenario. The story goes a little something like this:

We had a couple datamarts that held historical information that could not be recovered from the source so we had them marked "read-only" to prevent them from getting mucked up. These datamarts were copies of another datamart that we do update on a regular basis so they have really long names to explain why they're hanging around in a read-only state.

Well, after the SQL Server 2012 upgrade I didn't notice that the databases got stuck in the "recovery" state. The indicator that SQL Server Management Studio puts at the end of the database name was hidden because the database name was so long. Since no one actually uses these datamarts, no one noticed for a long time. That is, until I needed the disk space for something else. You can't detach a database in a recovery state you know.