SSRS conditional folder visibility

Question

I have two reports that I need to build. One that has a dozen or so columns. The other has the same columns + 2 extra. The first one is aimed at employees the second with the additional columns is aimed at Sr. Management.

I have a windows group set up for the proper Sr. Mgt users.

I am using SQL 2012.

I've done some SSRS stuff, but not enough to say I'm competent to do more difficult reports.

The problem I'm having is that we do not want the employees to see the sensitive information in those two columns. Frankly, we don't even want them to know the existence of a different report.

Option 1: I was thinking I can just create a folder in SSRS and add the report there and hide the folder. I created it and applied the security but it seems that everyone can see the folder. Maybe they can't edit anything in it or even maybe they can't read anything in it, but this solution, if unchanged, will not meet the goal of having them not even see it exists.

Option 2: I was thinking that I can use the UserID condition to hide the columns in the report and just create one report that differs depending on who was viewing. There are two issues that surfaced in my research. First, there is no facility for using Windows Groups instead of userid. That would mean I have to maintain the list of people inside the report and boy would that be a pain. And second, my understanding is that the export facility does not respect the column actions -- like hiding.

Am I making this too complicated? Is there an easier way to do this? With no other solution, so I need to put up another instance of SSRS for management and make them go back and forth?

Thanks for your time

Answer 1

I think we can just fix your problem, and avoid inventing a complicated and unnecessary solution:

Option 1: I was thinking I can just create a folder in SSRS and add the report there and hide the folder. I created it and applied the security but it seems that everyone can see the folder. Maybe they can't edit anything in it or even maybe they can't read anything in it, but this solution, if unchanged, will not meet the goal of having them not even see it exists.

Chances are that either you set up the security settings wrong, or there's a bigger configuration nightmare to worry about. What you should do is create the folder, go into the settings of the folder, and edit the security (thus breaking inheritance from the parent folder). Before even adding groups, you need to remove anyone that doesn't belong - namely entries like "YOU\Domain Users" - that gives access to anyone on your domain. Once you've cleaned out whomever shouldn't have access, you can add the users/groups that should. Problem solved.

Now, if that doesn't work, then it would seem to me that your SSRS instance is somehow granting everyone sysadmin access - check the Site Settings to see what users and groups are in the System Administrator role. Investigate any groups thoroughly - is BUILTIN\Administrators a sysadmin in SSRS? Check the group locally on the computer - is there another blanket domain group shown there?

If everyone on your domain has complete access to the SSRS instance, then your goal of "hiding" things is impossible.

Answer 2

Option1: You should not be able to 'browse' for folders unless the 'parent' level permission has an 'everyone' user set up to browse on the higher level. Set up a test account and RDP to a box you can use the test account on. Generally under 'Folder Settings' you set up permission and it cascades down until interupted. If you have a parent permission to browse and a lower one not to, they may be able to browse directories. You can ensure that the directory has ONLY dedicated users and the inherited settings are removed manually.

Option2: I would NOT do this. You will have a maintenance nightmare on your hands as you would have to determine in code who was what and update a list that would probably need to be updated somewhere in SQL or a service. As far as I know SSRS does not work with getting parameters and such directly from AD so you would have to code this time and again. For this reason and security context I would avoid this.

Option3: Set up a 'Subscription' to save the report to a file format(excel, pdf, word, etc) or email on a scedule and turn off permission for everyone but admins. If someone can still see the report or directory there is most likely a security context issue.

Option4: You can do a cheapy 'Hide in tile view' move that for most users will hide the directory unless they go to the URL directly and have access. Click on a folder then choose 'Folder Settings' then check 'hide in tile view' and hit okay. Directory is now gone for most part for regular users browsing in default mode.