Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
After successfully integrating SharePoint 2010 and Reporting Services and deploying my first Report Server Report to a SharePoint "Reports" repository using Abin Jaik Antony's MSSharePointTips article SQL Server Reporting Services Integration with SharePoint, what Repository or List permissions and security are required for report developers, publishers, and end users? Check out this tip to get this question and more answered.
Within a SharePoint 2010 repository, report definition files (rdl), report model files (smdl), shared data sources (rsds/rsd), and report part files (rsc) can easily be deployed and need to be secured. The permissions used to secure these objects are set in SharePoint Permission Levels. Out of the box, when a new site is created, SharePoint assigns a set of default permission levels to the 3 default users groups: Owners, Members, and Visitors. Upon the creation of a new "Reports" Library, these default groups inherit permissions from the parent site. By default, the Owners' group has full control which means that Members of this group can create, manage and set security on all report server items, while the Members group has the authority to add or publish reports and report builder models. Of course, both of these groups can view reports, and furthermore, members of the Visitors group can run reports and create user defined subscriptions. The MSDN articles Using Built-In Security in Windows SharePoint Services for Report Server Items and Sites and List Permission Reference for Report Server Items both have details references which match the SharePoint Groups to the SharePoint Permission Levels to the related Report Server Access items.
Configuring SharePoint Library Permissions
However, what do you need to do to adjust those permissions? For instance what if you do not want the Visitors group to be able to view reports or you do not want the members' group to publish reports? In order to change permissions for a Report Library / Repository, you will use the Ribbon > Library Tools > Library Permissions area. This tool is part of the standard Library Ribbon. To get to the Ribbon and the Library Tools menu, first navigate to navigate to your SharePoint site, and then to your Report Server Library; in the upper portion of the window, the Ribbon will appear similar to the below figure.
Next Click on the Library button which will open the full Library Ribbon; a partial view of this ribbon is pictured below. To access these Library Tools, a user will need to belong to a group which has either Full Control or Design permissions. Please note, these instructions pertain to SharePoint 2010 only.
Next, you would click on a particular group, as shown in the blue area in the screen shot below. Clicking on a group opens the Edit Permissions Window, the white area in the figure; this window permits you to assign or remove specific Permission levels. So for instance, if you wanted to grant Full Control Access to the Members group, you would check the Full Control box and then click the OK button.
Granular SharePoint Permissions
In some cases, you will want more granular control over the permissions you grant. In these cases, you could grant individual permissions such as Read access to a particular user or group that is not part of the 3 default groups. To make these type of changes, first navigate to your Report Server Repository and again click on Library Tools Ribbon.
Again Select Library Permissions.
Now Select the Grant Permissions option.
You can now add a user utilizing the below form. Following the preceding example, you could grant Read access to a particular group or user by entering their information in this form and then clicking OK.
Create a Permission Level for SQL Server Report Server
Last, if you have very specific permission needs, you can create a specific Permissions Level which caters to your exact Reporting Services needs. You will need Full Control permissions or belong to the Owners group to make the changes in the next few instructions. First, from your main SharePoint home page, Top Ribbon, Click on Site Actions and then Site Permissions.
Within Site Permissions, the Permission Tools Ribbon will display, and you will want to click on Permission Levels as noted in the below figure.
The Permission Levels screen is a bit confusing in that the Edit function is only available if you click on the existing Permission Levels, such as Full Control, Design, or Contribute. Certainly, you could add a new level also, but in this example, just click on one of the existing Permission Levels.
Now to adjust the permissions for a new or existing level, you would use the below screen. For instance, you could create a new permission level, which could be used to prevent a group or user from deleting reports, models, and other documents. Once this permission level is created you would assign it to the appropriate group using the instructions at the beginning of this tip. As you add Permission Levels, be sure to remove / uncheck the unwanted Permission Levels.
Integrating SQL Server Reporting Server with SharePoint 2010 allows users to navigate to one site for both their reporting and collaboration needs. When you integrate these two systems, you will need to setup SharePoint Security so users, publishers and developers of reports have the access they need to complete their jobs.
- To get a detailed cross reference of SharePoint Permission Levels to Reporting Services Permissions, please see Comparing Roles and Tasks in Reporting Services to SharePoint Groups and Permissions http://msdn.microsoft.com/en-us/library/bb283182.aspx.
- Check out these related resources:
Last Update: 2012-02-15
About the author
View all my tips