Row Based Security (RLS) on SQL Server Reporting Service (SSRS)

SSRS, one of the three services that SQL Server offers us free of charge, is actively used in daily business life. Particularly in companies with a large number of people reviewing the reports, a subscription fee is paid per person to cloud reporting applications. In order to avoid this, it can provide reports that it can get with SQL query by opening unlimited users over SSRS. Moreover, you can create a special domain for your company and use it in-house.

What is Row Based Security (RLS)?

It is a feature that comes with SQL Server 2016 that allows each user to see only their own data. In other words, users cannot see the lines other than the lines related to them.

Row-Level Security (RLS)

Can I filter the generated report according to the people I have given a username and password to?

Certain usernames and passwords are set so that they can log in to the opened reporting server. Administrators want to apply row-by-row security (RLS) to these reports. For example; Like A can only see B, C, D salesperson.

By using the username opened to person A, you can provide row-based security with the IF else loop in the SQL query.

Rather than the way I researched in the articles, I solved this with IF else in the query. I wanted to share this solution with you as well.

if (@UserID=’olgunaydin’)
begin
SELECT *
FROM
Satislar AS Orders
WHERE Orders.CalisanID IN (2,3,4)
end

Let A’s “CalisanID” be 1, B’s 2, C’s 3, and D’s 4th. Let the username opened to person A be “olgunaydin”. Person A will be able to see the B, C, D users he is responsible for, but will not be able to view other employees, when he enters the user name and password to open the report created thanks to this query.

One of the important issues here will be to assign the UserID in the Built-in-Fields to the created @UserID parameter in the parameters section. This UserID is automatically assigned to reports when you create the server. Thanks to the “Active Directory Authentication” on the services, it automatically maps the entered user.

Assignment to the parameter is made on this screen.

Window of UserID parameter

Then, in this window, the assignment should be made by coming to the “Default Values” section.

In this way, our SSRS server will RLS according to the person entering.

You can share your created SSRS report on your server by filtering it on a row basis.