Security in SSRS

Security is the primary concern while we create reports in SSRS or SQL Reporting Services. Because, in a corporate world, the manager holds different privileges compared to a salesperson. SSRS provides multi-level security which means, you can restrict the user to access the Report Manager, or Folders, or reports.

In this article, we will show you how to apply security in SSRS with a practical example.

Home, or Parent Level Security in SSRS

Before we start applying Security in SSRS, Let me open my Report Manager to show the existing reports on our Server. To change the Parent Level Security, Please click on the Site Settings hyperlink at the top right corner.

Security in SSRS 1

Once you click on the Site Settings hyperlink, the Report Manager will navigate us to the site settings page. In this page, we had three tabs, and we already explained the first tab in Change SSRS Report Manager Title, so please refer the same

Security in SSRS 2

Please navigate yourself to the Security tab to see the existing users in SSRS Report Manager. From the below screenshot, you can see that there are two users: Administrator (by default), and User with admin privileges.

Security in SSRS 3

To add New Role User, Please select the New Role Assignment option.

Security in SSRS 4

That will open the following page. Here you have to add User or group within the text box and select the appropriate Role. For the SSRS Security demonstration, we created a new Windows user called MSBI, and we are using the same user

Security in SSRS 5

Now you can see the new user (MSBI) with system user privileges.

Security in SSRS 6

Delete Top Level User Security in SSRS

To delete the user, Please check-mark the user and select the delete menu.

Security in SSRS 7

A message box displayed for the confirmation of the user delete. Click OK to delete, and cancel to Undo

Security in SSRS 8

Report Level Security in SSRS

To provide security for a single SSRS report, Please click the down arrow beside the report to open the menu items. Please select the Security option from the menu items.

Security in SSRS 9

Once you select the Security option, the following page will be displayed. To edit the default settings, please click on Edit Item Security option

Security in SSRS 10

After you click on the Edit Item Security, a pop up (confirmation window) box displayed for the confirmation. It will ask whether you want to add custom security, which is different from parent security. Click OK to add, and cancel to Undo

Security in SSRS 11

Next, click New Role Assignment option to add a new user and to assign a new role

Security in SSRS 12

Following are the list of roles available for each user in SSRS Security

  • Browser: This is the basic role that can assign to the user. A user with Browser role can View Reports, Folders, Models, and resources. Apart from this, a user can Manage individual Subscriptions.
  • Content Manager: This is the Highest role that a user can get. He can perform all the tasks that are available in the Report Manager.
  • My Reports: User with the role can only work within My reports folder. Apart from Security settings, he can do all the things
  • Publisher: User with Publisher role can create Linked Reports, and manage Data Sources, Reports, Folders, and Models
  • Report Builder: He can view the reports.

From the below screenshot, you can see that we are assigning the new user called MSBI, and assigned the Browser role to him.

Security in SSRS 13

Now you can see the new user called MSBI with the Browser role.

Security in SSRS 14

TIP: The above page had one more item called Revert to parent Security. By clicking this button will erase the setting that we made up to now, and assign the role that we specified in Home, or Parent Level Security example.

Folder Level Security in SSRS

To provide security at the folder level in SSRS, Please click the down arrow beside the folder (here it is Charts folder) to open the menu items. Please select the Security option from the menu items.

Security in SSRS 15

Once you select the Security option, the following page will display by the report manager. Follow the steps that we specified in the Report Level Security example

Security in SSRS 16

Add New User Role in SSMS

Instead of using the default user roles, we can use the SQL Server Management Studio to create a new role as per our requirements. To do so, please open the SSMS and select the Server Type as Reporting Services. Don’t forget the credentials to log in to the Server.

Security in SSRS 17

Within the SQL Management Studio, Navigate to Security Folder, then right-click on the Roles folder will open the context menu. Please select the New Role..

Security in SSRS 18

Once you choose the New Role.., a new window called New User Role will display. Please provide the Role Name and Valid description (describes the Role).

Next, select the tasks that you want to assign to this Role. From the below screenshot you can that, User can Create linked reports, View reports, and View Data Sources.

Security in SSRS 19

Now, please select the Bar Chart using the report Builder Report on the Home page, and go to the Security page. To show our newly created Role, let me click New Role Assignment option

Security in SSRS 20

Now you can the newly created Role. I think we forgot to add the description

Security in SSRS 21

You can also change the existing user roles.

Change User Role Settings in SSRS Security

Within the SQL Server Management Studio, Navigate to Security Folder, then Expand the Roles folder. Here you can see the default User roles. Please right-click on the Role that you want to see, and select its properties

Security in SSRS 22

Once you click on the properties option, a new window called User Role Properties will open. Here you can see the default roles allocated by the Reporting service. If you want, you can change this setting as well.

Security in SSRS 23