Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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....
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ToddChitt ,

     

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

    Thanks for you valuable input on this.

ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
3 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ToddChitt
      Icon for Super User rankSuper User
      3 years ago

      Anonymous 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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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?