Many SQL Server Reporting Services (SSRS) developers may not be aware that they can manage SSRS using Management Studio. While SSRS maintenance is typically performed through Report Manager online or Report Services Configuration Manager, there are several benefits to using Management Studio for certain tasks.
Connecting to SQL Server Reporting Services (SSRS) using Management Studio
To connect to the Report Server in SSMS, follow these steps:
- Click on “Connect” in Management Studio.
- Select “Reporting Services”.
- Fill in the server name and instance (if using a named instance) and choose the authentication method (Windows Authentication is most common).
- Click “Connect”.
The SSMS Object Explorer will then be displayed with three main folders: Jobs, Security (with Roles and System Roles subfolders), and Shared Schedules.
SQL Server Reporting Services Job Maintenance Options
The Jobs folder in SSMS allows for the review and cancellation of current running jobs in SSRS. A job in SSRS can be an on-demand report run by a user, a manual creation of a report snapshot, a manual creation of a report history snapshot, a currently running standard subscription, or system jobs started by the report server.
To cancel a job, simply right-click on the job name in the Object Explorer and select “Cancel Job(s)”. A confirmation window will appear, and clicking “Cancel” will abort the running job without warning.
SQL Server Reporting Services Security Roles and System Role Maintenance
The Security Role Maintenance area in SSMS allows for the creation, maintenance, and deletion of regular roles and system roles in SSRS. Regular roles include Browser, Content Manager, My Reports, Publisher, and Report Builder, while system roles include System Administrator and System User.
To create a new role, right-click on the respective folder and select “New Role…”. Fill in the name, description (optional), select the appropriate tasks, and click “OK”. Regular tasks and system tasks can be found in the Microsoft documentation.
To maintain a role, right-click on the role to be changed and select “Properties” or double-click on the role. On the role properties screen, tasks can be added or removed by checking or unchecking individual tasks and then clicking “OK”. Roles can be deleted by right-clicking on the role and selecting “Delete”. Exercise caution when deleting roles to avoid locking yourself out of a folder or report server.
SQL Server Reporting Services Subscription Shared Schedules
Shared schedules in SSRS are used by subscriptions to run reports at specified times on a set schedule. In SSMS, you can create, maintain, and delete shared schedules.
To create a new schedule, right-click on Shared Schedules and select “New Schedule”. Fill in the name, start date, end date (optional), and recurrence pattern. Based on the recurrence pattern type selected, different scenario and selection boxes will appear. Once the setup is complete, click “OK”.
To maintain a schedule, right-click on the desired schedule and select “Properties” or double-click on it. Similar to the new schedule screen, customized schedules are defined by naming the scheduling and selecting a recurrence pattern. The “Reports” option in the upper left corner of the properties page provides a way to see all the reports assigned to the selected schedule.
To delete a schedule, simply right-click on it and select “Delete”. Take care when using the delete option, as it cannot be undone.
Conclusion
While not widely known, SQL Server Reporting Services can be managed using Management Studio for specific functions. These functions include role and system role creation, adjustment, and removal, shared schedule creation, maintenance, and deletion, as well as job review and cancellation. Using Management Studio provides an easy method to perform these tasks without having to go to the Report Manager website. However, certain tasks such as content maintenance and additions can only be performed in Report Manager on the web.