Creating Custom SSRS Security Roles

When you have a consolidated environment, usually you don’t want to set very broad permissions to users, most of the times you only want them to see what is really necessary.

Doing that in the Database Engine is quite easy, but what about Reporting Services? We know that SSRS exposes the following default roles: Browser, Content Manager, My Reports, Publisher and Report Builder. However, for me sometimes those roles offer too much to the user.

What people usually don’t know is that you can connect to your SSRS Instance using SQL Server Management Studio and then create customized roles. So, let me show you how to do it.

First of all, open Management Studio and connect to your SSRS Instance.

image

PS: Do not forget to change the Server Type to Reporting Services

After that, this is the view that you have it.

image

From this point, what you have to do is right click on Roles and create a new role.

image

In my case I only want to enable people to View Reports and Folders that I grant permission to and nothing else. So, all that you have to do is select those two settings as shown in the screen.

Now that you have your role created, it is time to have it setup in the reports.

As you can see below I have two folders.

image

In my example I only want to grant viewer permissions to the first folder and keep the second one hidden. To do that click in Manage Folder as shown below.

image

Now, click on Add group or user

image

By default BUILTIN\Administrators will always be there. In the Group or User area type the domain along with user or group account and select role that we have just created.

image

Now, you have to pay some attention to this point. After you have added the user or group in the root folder, the same permissions are applied to the child folders, therefore this means that for all folders below root the account will be there and the users will be able to see it. Because we don’t want that, we have to go on each folder where the account should have no permissions and delete it from there.

On each folder where the user should have no access, go to the manage settings and click in the Security tab and then click on Customize security

image

This pop up will show up to you, and you just have to click it on OK.

image

Select the user and click to delete.

image

Now, when you refresh your screen, you are just going to see the needed folder.

image

In case you ask me if there are easier ways to do it. YES!!! Powershell is the way to go for that. I will not do it in this post, but I will do a second one showing how to achieve the same thing with Powershell.

I hope you liked!

Marcos Freccia

Advertisements

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on November 6, 2017, in Dicas, VirtualPass and tagged , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: