Refresh Sharepoint list without being the list owner

[shady_hamilton]

I have a dashboard on my Desktop app that connects to several SharePoint lists. i granted few users read and edit rights on the sharepoint lists and the PBIX file. 

they can open the file, but they can't referesh the data. it always gives the same error for all the databases "The column XXX of the table wasn't found"

it only works if i give the users Owner rights on sharepoint, which is a huge risk.

Is there a solution or explanation please?

[v-cgao-msft]

Hi @shady_hamilton ,

You're sharing just the .pbix file, which doesn't contain the data source credentials. Please make sure they have at least Viewer permissions for sharepoint lists first:

If they already have Viewer permissions, it could be a caching error. Please go to “Data Source Settings” and clear all permissions - then recapture them.

You may also consider using Dataflow to unify connections and manage connection permissions.

Introduction to dataflows and self-service data prep

Creating a dataflow

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[shady_hamilton]

Users already have edit rights over the SharePoint List, I even tried giving them Full Control over the list and that didn't work. I tried clearing the permissions too, but that didn't work too.

[rajendraongole1]

Hi @shady_hamilton - Power BI treats the connection as an API access to SharePoint, which sometimes requires broader permissions than simple list viewing or editing.

Instead of making users Owners of the SharePoint site, you can create a new SharePoint permission level with "Full Control" only for the lists they need access to.
This way, you avoid granting Owner access to the entire SharePoint site and only provide elevated access to the specific lists.
To do this, go to Site Settings > Site Permissions > Permission Levels and create a custom permission level that grants "Full Control" on the relevant lists only. Then assign this permission level to the users or group.

Set up a service account with "Owner" or elevated permissions on the SharePoint lists.
Share the PBIX file with other users but configure the data source credentials to use the service account’s credentials for refreshing.
This approach can centralize the permissions, but it may not be suitable if the users need to access sensitive data directly.

Publish the PBIX file to the Power BI Service and configure the data source connection there.
In Power BI Service, set up scheduled refreshes using an account with "Owner" or "Full Control" permissions on SharePoint lists. This bypasses the need for users to refresh data locally.
Users can then view the refreshed data without needing elevated permissions, and this keeps the refresh process centralized.

hope this helps and let me know if you need more details

[shady_hamilton]

Hello, thanks for the help, but that didn't work.

I created a group and gave it Full control rights over the SharePoint List and included one user to test. the user downloaded the PBIX file and tried to referesh, he got the same error. The column "ID" of hte table was not found!!! If i restore the Owner rights for this user over the whole site, the referesh works normally!!

I didn't get the service account thing, I am not publishing any report, The users are opening it using Power BI desktop