Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services SSRS 2017 Website Security and Options



By:
Overview

Once a Report Server report is completely designed, the next step is post or deploy the report for all to see. After deploying a report (and likely planned beforehand), security needs to be set on the SSRS website. Additionally, there are several options on the website that need to be considered as reports are deployed. 

To start off the process, we will assume that Visual Studio—SSDT-BI is being used to design a data source(s) and report(s) along with any necessary parameters. Furthermore, we will assume the report design is complete and ready for report consumers to review, and finally that some reports have been successfully deployed to the website.

SSRS Security

Security on the SSRS website utilizes a several layer approach. At its highest point, there are System Level Roles which allow for control of major, system wide settings.

site settings - Description: site settings

Users assigned to the System Administrator role are the main administrator of everything on the site; members of this role have full control over the site and can modify role assignments, role definitions, various site wide properties, and create subscription schedules. The system user only has the ability to view these same properties, and role also has the ability to execute Report Builder reports. In either case, a local/Windows group or user can be added using the Add group or user option.

system roles - Description: system roles

Moving beyond the system level roles, permissions must also be applied at the folder and report level on the Report Server. Similar to the System Assignments, a local or an active directory user or group can be assigned to one or more roles. SSRS includes 5 predefined roles that should suffice in most circumstances. These roles include:

  • Browser-allows users to run reports and browse folders; this role will be used by most end users
  • Content Manager-allows users to manage and define folders and reports and to grant permissions
  • Report Builder-allows users to create Report Builder reports
  • Publisher-allows users to deploy / upload reports and create folders
  • My Reports-allows users to create and maintain personal MyReports folders

Using the MSSQLTipsProjects folder as an example, in the below illustration, we will set the permissions for the folder. Setting up permissions for a report is completed in the exact same way, except you are managing the report security and not the folder security. To begin the process, first select ellipse button ( 3 dots ) to the right of the folder and then pick Manage. 

manage folder - Description: manage folder

Next select the security option.

manage ssrs

To set the security for this particular folder, the Customize security option must be selected. The following pop up warning will be displayed. Some additional attention needs to directed to how permissions are inherited by subfolders and reports. First, the role assignments use a waterfall methodology. Thus, starting at the home page, all folders, subfolders, or reports underneath the home page, in the hierarchy, will have the same permission that are assigned to the home page UNLESS the permission chain is broken, either at the folder, subfolder, or report level. Clicking on the Customize security button, as displayed above, will break the permission chain and allow for the customization of the security for that individual folder or report.

confirm custom security - Description: confirm custom security

We now have the ability to add users to the permissions list for this folder (or report if we were using a report) by selecting Add group or user.

add user to folder permissions - Description: add user to folder permissions

A windows group or user must be entered, and then the appropriate roles for that user or group should be selected.

select roles - Description: select roles

Permissions are then set for this user for the selected folder or report.

add user complete - Description: add user complete

Fortunately, if at some point you would like to go back to having your folder or report inherit its permission from a parent structure, you can click on the Use same security as parent folder button.

manage ssrs

The third layer of security is at the data source level. Specifically, for all data objects (i.e. tables, views, stored procedures) used in a particular report, the user running the report must have appropriate permissions to access the data and its related object (i.e. select permissions for tables and views and execute permissions for stored procedures). Thus, by reviewing the data source properties, we are able to set the name and password authentication that gets passed to the database when running the report. As seen in the second screen print below, the credentials being passed to the database can be:

data source permissions - Description: data source permissions
data source permissions 2 - Description: data source permissions 2

Of course, whatever option is designated, all objects involved in the dataset queries for the report must be granted appropriate permission including execute permissions on a stored procedure or select permissions for a group of tables and views.

Now that security is set, let us move on to some of the other options and properties that can be set on the website.

Manage Folders and Reports

Each folder on the report server site contains the ability to move, delete, or manage.

manage folder - Description: manage folder

Move and delete are self-explanatory where as the manage option provides the ability to set security (which we previously discussed) along with the capability to rename, add a description, or even hide the folder from view.

manage folder - Description: manage folder

Likewise, each report can also be managed in a similar way including moving and deleting the report. Moreover, the Manage option includes the ability to open (aka run) a report, edit the report in report builder (proper permission must be granted), or download the report rdl file.

manage reports - Description: manage reports in

The add to favorite option is a new addition in SQL 2016.

add to favorite - Description: add to favorite

Selecting this option adds a link to the report in your favorites list.

favorite link - Description: favorite link

Additionally, we can set the reports description, its visibility, and its timeout settings.

ssrs properties

The Parameters tab allows the reports parameters to be changed including the default value. Note this option overrides any options set in SSDT-BI.

report parameter - Description: report parameter

On the data source tab, the data source can be adjusted; for example, maybe a report needs a different data source for permission reasons.

design grid properties

The dependent items option shows if any other reports are dependent on this report (such as a subreport), while the Caching and History snapshot options allow for setting how SSRS saves previous runs of the report.

We will discuss the subscription option next.

Subscriptions

From the SSRS website, subscriptions to a report can be added and managed. To add a subscription from the manage report window, select the subscription option.

 ssrs manage grid

Next, we set the subscription description, the type of subscription (note data driven is enterprise edition only), the schedule, the destination (save to a file share or email), and finally the parameters to use when running the subscription. For the destination, one of many export types can be selected including Excel, Word, and PDF.

new ssrs subscription
new ssrs subscription

As the report subscriptions are executed, using email in the above example, the subscription status can be reviewed on the subscriptions status page. The subscription can also be disabled, deleted, or run immediately from this same screen.

subscription status - Description: subscription status

The final resulting email for the example subscription is shown below.

subscription email - Description: subscription email

Search

One last item that is often very helpful, but also often overlooked is the search functionality. The search process compares names and descriptions with the search term and will skip folders and reports which the user doing the search does not have access to.

search window - Description: search window

Now that the report security and other options on the SSRS website are setup, we will move on to using SSMS to manage SSRS.

Additional Information

Last Update: 12/29/2017




More SQL Server Solutions











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.



    



Learn more about SQL Server tools