Reporting Services provides a role based security model and as such, allows you to create roles and assign appropriate permissions (tasks that they are able to perform) to that role. Roles control the accessibility of items on the report server. Roles are roles, but there are two general kinds of roles: User and System. User roles assign appropriate permissions to the users themselves. However, you can also assign users or groups to System roles. Just know that depending on which kind of role is created, different tasks are available.
A system role can be used to assign appropriate permissions at the system level. Users and groups can then be added to this system role. A common use of this is with the Report Builder. In this article, I am going to show you how to create a new system role specifically to achieve this task.
Out of the box, Reporting Services has two system roles: System Administrator and System User. Those in the System Administrator role can do anything it takes to manage reports as shown in figure 1 below.
Figure 1: System Administrator Role
The system user role is pretty low on the totem pole. This user can really only view report server properties and view schedules. They do have a little bit more permission than that, but you can see in figure 2, they have far less permissions than the system administrator role above.
Figure 2: System User Role
Now that you have more details on the built in roles, let me show you how you can create your own custom role.
- Fire up SSMS and connect to the reporting services instance
- Navigate to the Security Folder
- Right click on the System Roles folder
- Select New System Role…
- Name your new role, enter a description (optional) and select the appropriate permissions that you want to grant them. In my case, I have chosen to allow Execute Report Definitions. This will provide them with the ability to see the Report Builder button in Report Manager. If you’ve been following along, you should be seeing something similar to that shown in figure 3.
- Click OK to save your new system role.
Figure 3: Custom System Role
For this example, I am going to use a test user called BKMAdmin. As of right now, this user cannot see the Report Builder button when navigating to the Report Manager. This can be seen in figure 4 below.
Figure 4: Test User without Permissions
Now that you have a new system role and you have a user that you want to grant access to, you’ll need to add the test user to the new role from within Report Manager. Navigate to the Site Settings > Security tab of Report Manager as shown in figure 5 below.
Figure 5: Site Settings – Security Tab
Now click on the New Role Assignment as shown above. This will bring you to the New System Role Assignment Screen as shown in figure 6 below.
Figure 6: New System Role Assignment
The preferred method here would be to add a group, but since I am on my local laptop, I don’t have a group to add. As such, I am going to add my test user and assign him to the BKM_Report Builder Users system role that we created. Click OK to save the new assignment. After logging in as HP\BKMAdmin, I will now be shown the Report Builder button when navigating to the Report Manager.
Figure 7: Test User with Permissions Now
There you go! You have now created a new system role and assigned a test user to that role, thereby giving them access to the Report Builder button. I hope that you enjoyed this article.
Until next time, “keep your ear to the grindstone” – Good Will Hunting
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting
Email: firstname.lastname@example.org | Blog: BI Developer Network
Convert with DTS xChange | Develop with BI xPress | Process with TaskFactory | Document with BI Documenter