Setting up Permissions for Report Builders in SSRS 2005: Don’t give them elevated permissions.

4/28/2008 12:00:00 AM

By RON HOVLAND

One of the features of SQL Server Reporting Services (SSRS) is the Report Builder tool that allows an individual to create ad hoc reports. An individual in one of my classes stated that he didn’t like the fact that he had to create a custom role in order to make the Report Builder button appear in the Report Manager for the groups of people allowed to use Report Builder. His biggest complaint was that the new, custom role required his giving these folks rights that were too elevated. He also stated that there were no good informational sites, as he had been searching for months.

This puzzled me, so I went to my favorite search site and typed “Report Builder Permissions.” The first hit was Microsoft’s, and the instructions were quite clear as to what permissions were required. And they weren’t the elevated ones that my client had set for his users. As we chatted about the requirements, it became clear that he didn’t understand Microsoft’s SSRS security architecture as fully as he needed. One of the elements regarding SSRS security that often causes confusion for new administrators is the two-pronged approach Microsoft took: Site-Level Security and Item-Level Security. The Report Manager site was designed as a one-stop shop for everybody utilizing SSRS. It is where users go in order to view reports. It is also the site used by administrators that manage SSRS. Therefore, Microsoft broke security into two distinct layers: Report Management and Site Management. In many cases, users simply need to view reports, thus allowing the site administrator to simply set the Item-Level security roles for them. When users, however, require site-level privileges, the administrator can then work with a completely different set of roles in granting privileges to the users.

Report Builders need both item-level and site-level privileges, especially if you want that ReportBuilder button to magically appear for those specific users. Assuming that these users are part of a Windows Group called ‘Report Builders’, you would assign that group the Item-Level security role of ‘Report Builder’ to the appropriate folders. You would then simply need to add that Group to the Users role at the Site-Level. That role has minimal privileges for the Report Manager site, but one of these privileges is to be able to see (and use) that button (which launches the Report Builder installation).

The following image shows a section of the Report Manager’s Site-Settings page. To set up permissions for Report Builder, the administrator must use the “Configure site-wide security” link.

The Site-Level Security Page is used to assign roles. Note that, in this example, the ReportBuilders Windows Group is added to the System User Role at the Site Level. This is one of the requirements to make the Report Builder button available to this group.

To add the appropriate permission at the Item-Level, set permissions at the home folder (or a sub-folder) by clicking the Folder Properties tab, then by clicking the “New Role Assignment” button and checking the Report Builder role. Click the OK button and the role will have been assigned, as shown below. This step is the other requirement in making the Report Builder button available to the people who will have rights to use that toolset.

Ron Hovland

Business Intelligence News