Forum Discussion

mmcanelly's avatar
mmcanelly
Helper II
2 years ago
Solved

Object-level security for Analyze in Excel

I am working on implementing object-level security (for specific columns) on a couple of datasets that users primarily access via Excel (through the Import from Power BI functionality for pivot tables). For some reason, the object-level security is not working when accessing the dataset in Excel.

 

I have tried the following with the test account that is set up to have limited access to the data through object-level security:

  • Connect to dataset from Power BI Desktop - object-level security is working
  • Create a report from dataset within the Power BI Service - object level security is working
  • Use "Analyze in Excel" from the service to download an Excel file - user gets full access to dataset
  • Start in Excel and access dataset from Insert > Pivot Table > From Power BI - user gets full access to dataset

Is this a limitation of object-level security where it's just not going to work within Excel, or am I doing something wrong?

Need Help
  • mmcanelly's avatar
    mmcanelly
    2 years ago

    Hi GilbertQ, this magically started working for me today. There seems to be some sort of conflict when I'm using the test account in Excel but logged into Windows with my main admin account. It seems to be confused about who's accessing the dataset somehow, but I haven't figured out exactly when it happens. So it doesn't look like it's an issue with the object-level security at all, but something account related. Thanks for taking the time to try helping with this!

5 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    2 years ago

    Hi mmcanelly 

     

    Have you added the user to the Role in the Power BI Service?

     

    Also the user cannot be a member of the app workspace or higher as that will allow them to see everything.

    • mmcanelly's avatar
      mmcanelly
      Helper II
      2 years ago

      Hi GilbertQ,

      Yep, I've set the roles up for the data source in the Power BI service. The user I'm testing doesn't have access to the workspace, but has been set with Read and Build access to the dataset.

       

      Here are all the settings I have:

      • Roles created in Power BI desktop
      • Object-level security set up on the columns in the tabular model
      • Account added to the Limited Access role in the security settings for the dataset in the Power BI service
      • Account given Read and Build permissions in the dataset permissions

         

      This all works as intended and limits the columns the user can see when using the dataset within Power BI Desktop or Service. The only place it isn't working is within Excel (Analyze in Excel from the service or loading a Power BI dataset from an Excel pivot), which is where I really need it.

      • GilbertQ's avatar
        GilbertQ
        Super User
        2 years ago

        Hi mmcanelly 

         

        It is working for me when I have set up Object Level Security. Can you confirm that you have set the column permissions on the table?

  • GilbertQ's avatar
    GilbertQ
    Super User
    2 years ago

    Hi mmcanelly 

     

    Ahh yes that has happened to me before. I have not found a way to force it to log in as another user. The only other option is to change the connection string to log in as the assumed user!