Just found the answer to something that has been irritating me slightly for ages: how to give a user access to a report without giving them access to all the other reports in the folder.
We have folders that are based on the roles people perform in the company. We granted the appropriate domain groups access to their folders in the Browser role. That gives those people access to the reports in those folders because, by default, a report inherits its security from its parent folder. This works great until the Sales Manager says, “Can you give Student Joe access to the Sales Stats report so he can do a special job for me?” This report is in the Management folder which also contains a load of sensitive reports that Student Joe cannot be allowed to see, so you can’t give him the Browser role on the folder. You can override the inherited security on the Sales Stats report by putting Student Joe in the Browser role at that level but he still can’t get to it in the Report Manager because he has no rights on the Management folder. His only access is via a URL direct to the report.
We thought we were stuck with this and had got used to it. After all, people could get to what they needed so there wasn’t much of a business case for sorting it out properly. But today I got another such request and the red mist seized me. (Perhaps it’s the weather?) I couldn’t believe this was impossible. Surely they thought of this? And, thanks to the excellent Hitchhiker’s Guide to SQL Server Reporting Services, I discovered that they did. They just didn’t go out of their way to mention it.
Here are the steps:
- On the Home page, click on Site Settings.
- Click on Configure item-level role definitions.
- Create a new role (say, View Folders Role) and assign it the View folders task.
- Now you can assign a user or group the View Folders Role on a folder and they will be able to navigate to the folder and there they will see any reports you have given them access to.
Easy when you know how!