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

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.

8 Replies

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