Can we create RLS from my sql server instead of in power BI?

[Sampathkumar_v]

Hi Folks,

I am new to this concept in power BI.

Can we create RLS from my sql server instead of in power BI?  If, possible how are we going to map bases on user login's from power BI service? e.g HR team should view data only HR related, Sales team should view data only sales related, etc....
Actually, I am giving edit permission to my user's (do there own visual at server side) If I give other then viewer access to my user the RLS will not work that is the reason thinking to implement at database side itself. Is it right approch to implement at database side?

Can any one help on this post.

[ToddChitt]

Yes, this is entirely possible. 

As I understand it, you want to control who can see what (RLS) in Power BI based on data in SQL Server, correct? 

There are a few ways to do this. 

What type of data do you have? Do you maybe have a column with email address? Cam you use the USERNAME or USERPRINCIPALNAME functions?

[Sampathkumar_v]

Hi @ToddChitt ,

Yes, we can use username or userprinciplename to map the users. my point here is I don't want to implement any RLS concept from Power BI desktop using username or userprinciplename I need to implement at DB end. Actually I connecting stored procedure using Direct query not a table in that case My SP should have implement RLS in that case how we can map SP with bases on login user from power bi service? 

[ToddChitt]

@Sampathkumar_v Are you saying that you want to pass the logged in user credentials from Power BI into your stored procedure as a parameter, then let the Database engine determine what rows to send back? I don't think you can do that. 

What is the issue with simply loading ALL data up into Power BI, then letting THAT engine determine what rows are DISPLAYED? 

Suppose you have

* A table of Customers with: CustomerID, SalesPersonID

* A table of SalesPeople with SalesPersonID, EmailAddress

* A table of Sales with: CustomerID, SaleDate, SaleAmount

* A One-to-Many relationship from SalesPerson to Customer

* A One-to-Many relationship from Customer to Sales

You can simply add a DAX Security filter in Power BI on the SalesPerson table with something like this:

[EmailAddress] = USERNAME().

Now, I recognize that your data is probably a lot more complex than this, but honestly, I don't think Power BI can pass in the UserName to your stored procedure. You may have to go with this type of approach.

[Sampathkumar_v]

Hi @ToddChitt 

Thanks for your input to understany my requiremnt.

Concern 1: I have a SP having seven input parameters I should execute all 7 input param using dynamically this seven parameter has having type conversion. 

e.g location field has a input parameter one of the SP (To get the location name in SP the logic is decalred param in SP div datatype int to map the location from a table decalred data type is varchar) same like goes with other input parameter. Is it right apprach to call SP with all those input parameter or View is good approach in this scenario. please advise

concern 2: can we connect SP using Direct connection? I have red few docs no where telling it can be possiable.

Concern 3: can we do incremental refresh using SP? 

[ToddChitt]

Forgetting about Incremental Refresh and Direct Query, I want to understand your statement here:

I don't want to implement any RLS concept from Power BI desktop using username or userprinciplename I need to implement at DB end

WHY? Is this a personal preference?

It's like saying "I want to start my car using my left hand and work the gas and brake pedals with my left foot." WHY? Is that your personal preference? Cars (at least in the US) are designed to be started with the RIGHT hand, and operated with the RIGHT foot.) If your answer is because you have no right hand and no right foot, then OK, let's see what we can do. 

But if it is simply a matter of because you WANT to do it that way, I suggest you think about doing it in a way that is EASY. You can still do a stored procedure (IMPORT method) and implement RLS in Power BI on top of that data.

By the way, Increment Refresh is for IMPORT, won't work with Direct Query.

[Sampathkumar_v]

Hi @ToddChitt 

Thanks, My end user want to do there own visuals once report published from power bi desktop. As per Power BI documents Other then viewer role we will not able to implement RLS in power BI service because of this concern I have asking Is there any way can implemt RLS from database using Store procedure itself? If, we can implement at DB side? what is the procedure to restruct underlining data based of user level access? Sorry I am telling same thing again and again to make you to understand my requirement.

[ToddChitt]

OK. Now we are getting to the root of the issue. Your requirements are that A) there be Row Level Security on the data so users can only see THEIR data, and B) Create their own visuals. 

Please investigate the options for Publishing the APP. On the "Audience" tab, there are check-boxes on the right, in particular, one for "Allow people to build content with the datasets in this app audience."

Users building their own visuals when you enable this setting should still be bound by Row Level Security. 

[Sampathkumar_v · Accepted solution]

Hi @ToddChitt ,

Sure, I will explore on App settings in power BI service.

Thanks for you valuable input on this.