Data Build permissions

[nik751]

Hello,

I have a question concerning dataset permissions. I'm attempting to give users a dataset based on a SQL query from SQL Server databases. I want users to have access to all of the tools they need to create visualizations, execute transformations, and so on. However, I don't want to give them any access to the SQL server databases(not even read-only permission). What are my choices in this situation?

Power BI datasets appear to be the best option based on the possibilities I researched. Also, within my dedicated workspace group, I can control the dataset (refreshes, etc.) and the user can utilize the dataset as needed. The main difficulty here is that the user does not have access to transformation, i.e., the ability to use the Power Query editor.

Is there any other way such that when I publish the dataset in the dedicated workspace and share it with the users, they not only have the built permissions to create measures & visuals but can also use the Power Query editor without needing any rights to connect to the SQL Server database?
Thanks.

[DataInsights · Accepted solution]

@nik751,

Dataflows are a good option for this scenario. A dataflow is the online (PBI Service) version of Power Query. You can create a dataflow that imports data from the SQL Server databases. Then, users can connect to the dataflow from PBI Desktop and do additional transformations in local Power Query. Users will need Viewer permission to the workspace where the dataflow resides.

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-serv...