SQL Server Reporting Services SSRS 2017 and Management Studio




By:
Overview

A little-known feature of SQL Server Reporting services is that several options can actually be managed via Management Studio. The three main options center around security role management, shared schedules for subscriptions, and job listings.

Role Management

The security Role Maintenance area serves two similar purposes: the creation, maintenance, and deletion of regular roles and the creation, maintenance and deletion of system roles. When you install SSRS, five regular roles (Browser, Content Manager, My Reports, Publisher, and Report Builder) and two system roles (System Administrator and System User) are created as noted below. Each of these roles are assigned a predefined set of permissions or tasks.

system roles - Description: system roles

New roles can be added, and existing roles deleted (although I do not recommend deleting any of the predefined roles).

role option - Description: role options

When creating new roles, the role just needs to be named and then the appropriate tasks to be assigned to that role are selected.

add normal role - Description: add normal role
new system role - Description: new system role

To maintain the role, right clicking on the role and selecting properties provides a way to update the tasks assigned to that role.

edit role properties - Description: edit role properties.

Shared Schedule Management

Shared schedules are used by subscriptions to execute reports at predefined times. To create a new schedule, right click on Shared Schedules folder and then click New Schedule.

Creating a new schedule is illustrated below. The following items needed to be completed:

  • Name
  • Start date
  • End date (optional)
  • Recurrence pattern

Based on the recurrence pattern selected, different scenario and selection boxes will appear in the pattern area.  For instance, if you select Week, the repeat interval, the start time, and the days will show (as noted below). When the setup is complete, click OK.

shared scheduled setup - Description: shared scheduled setup

These schedules can be deleted if necessary, or you can select properties to maintain an existing schedule. Care must be taken about deleting schedules as a schedule can be deleted even if it is being used by a subscription.

delete shared schedule - Description: delete shared schedule

However, within the properties window, clicking on the Reports tab provides a list of all report subscription which use the selected schedule.

report schedule - Description: report schedule

Jobs

The Jobs folder allows for the review and canceling of current running jobs.  A job in SSRS is any of the following:

  • On-demand report run by a user
  • Manual creation of a report snapshot
  • Manual creation of a report history snapshot
  • Currently running standard subscription
  • System jobs started by the report server

As shown below, double clicking on a particular job displays the job's properties including: the status, the type of job, the job action (render meaning the report is being run or rendered on the report server), the report name, the server, the user name, and the start time.

Management Studio provides quick access to several security and subscription features. We will conclude our tutorial series in the next tutorial by discussing backup methods for 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.





Monday, October 14, 2019 - 8:45:55 AM - Scott Murray Back To Top

It sounds like you need to query the reportserver database to get a list of subscriptions.  Eric Blinn's tip may be helpful for that: https://www.mssqltips.com/sqlservertip/6001/ssrs-reportserver-database-overview-and-queries/


Saturday, October 12, 2019 - 3:44:56 AM - srinivas Back To Top

I wanted to see the reports that are scheduled in ssrs 2017 report manager, same can be seen in 2014 ssrs report manager url. please help me.



download





get free sql tips

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