Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SSRS report server permissions when integrated with SharePoint


By:   |   Last Updated: 2012-02-15   |   Comments (8)   |   Related Tips: > Sharepoint

Problem

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.

Solution

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.

Library Tools

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.

Library Tools 2

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.

Library Permissions for Group

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.

Library Permissions Ribbon1

Again Select Library Permissions.

Library Permissions

Now Select the Grant Permissions option.

Library Permissions 3

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.

Grant Access

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.

Individual Permissions 2

Within Site Permissions, the Permission Tools Ribbon will display, and you will want to click on Permission Levels as noted in the below figure.

Library Group Edit

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.

Individual Permissions 2

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.

Library Permissions

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.

Next Steps


Last Updated: 2012-02-15


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 18, 2013 - 5:27:21 PM - Kevin Back To Top

I managed to get an answer from Microsoft about the subscription permissions issue.  Here's the thread:

http://social.technet.microsoft.com/Forums/en-US/sharepointadminprevious/thread/e9bf9948-f4c3-4883-8827-16a5cab5516e/

The basic answer is that this behavior is intentional.  "The thought behind this was also that, in most organizations, creating a subscription is a relatively high-privileged operation since it could have some performance impact and could affect the security of the data and stability of the server"

The Product Group might revisit this decision.


Monday, December 10, 2012 - 11:09:07 AM - Kevin Back To Top

That's good to know so that I can stop searching.  Thanks for your help!


Friday, December 07, 2012 - 8:33:05 PM - Scott Murray Back To Top

Actually I was able to recreate the issue on a Windows 7 install of SP and SSRS.  I wonder if it has something to do with UAC.  Otherwise, I think you are correct... it is a bug!


Friday, December 07, 2012 - 4:58:47 PM - Kevin Back To Top

I have tried the Manage Alerts option, it does not work.

The error says:

"Permissions granted to user ... are insufficient for performing this operation"  It then provides a correlation ID in the form of a GUID.  I located the GUID in the error logs but I was unable to find anything useful there.

 

So far, the only permission I have found that works is "Edit".  On your system are users able to subscribe without the edit permission?  I would be interested to know if this is truly a bug or if it is a problem with how I have things setup.

 

Thank you for taking the time to reply, I really appreciate it.


Thursday, December 06, 2012 - 8:36:16 PM - Scott Murray Back To Top

What error do you get?  Have you tried the Manage Alerts option?


Thursday, December 06, 2012 - 4:01:38 PM - Kevin Back To Top

Is there any way to allow users to create subscriptions without giving them "Edit" permission?  The "Create Alerts" permission seems to enable the button on the context menu, but an error appears after they click "Add Subscription" unless they have "Edit" as well.

Any suggestions would be great!

Thanks for the article.


Wednesday, November 14, 2012 - 7:04:30 PM - scott Murray Back To Top

the report creator is not involved.... check the list permissions


Wednesday, November 14, 2012 - 5:21:44 PM - Victor Back To Top

I have a question for you, we had a functional admin create reports in SharePoint, that person has now left the department and his account inactive. The problem is when his account is inactive we can't access the reports. So for now we change his account back to active. Do you know how to/where to change the creater ID so we can put another ID (service accout if needed) or is there a way to create the reports without a persons ID tide to it?


Learn more about SQL Server tools