Give another user a Power BI Service report for customization?

[Dimani]

Hi,

New to Power BI Service and Power BI data gateway.

We have a report that is using a SQL server connection through the Power BI data gateway. The connection is using my credential.

Now a user wants to have a copy of the report and customize it.

I can send him a copy of the report file, BUT can he simply use the existing connection that I created without my explicitly adding him to the SQL server database, customize the report in Power BI desktop and publish it to his workspace?  Do I need to share the data gateway with him?

If he only needs some of the data to customize the report, would creating a role in Power BI Desktop and setting the security in Power BI service after the report is published to PBI, be the way to go to limited the data he can see/use.  In this scenario, do I share the file/semantic-model and give him BUILT permission, instead of sending his the file?

By the way, the report had DirectQuery only, if that makes any different.

Thanks

[v-agajavelly]

Hi @Dimani ,

Just wanted to follow up and confirm that everything has been wrapped up on this. Please let me know if there’s anything pending from my side. Please feel free to reach out Microsoft fabric community forum.

Regards,
Akhil.

[Dimani]

Hi Akhil,

Thanks for following up.  More than a week ago, we set it up for the user to be able to customize our existing reports.  So far, I have not heard anything from the user.  Cannot say whether no news is good news, or he has not tried it.  Usually I take the former.  I think we can consider this is closed.  If we need further assistance, will reach out to the community again.

Thanks a lot for those who had helped!

Warmest regards,

Dimani

[v-agajavelly]

Hi @Dimani ,

Just following up again were you able to get the DirectQuery connection working as expected? Sometimes it takes a couple of tweaks with the gateway settings or role assignments. If it’s still acting up or if you’ve moved on to configuring RLS or anything else, feel free to drop a quick update. Happy to jump in and help troubleshoot further if needed.

Regards,
Akhil.

[v-agajavelly]

Hi @Dimani 

Just checking in to see if the guidance from @Akash_Varuna  helped clarify the issue with DirectQuery access. Granting gateway and database access typically does the trick, but if you're still running into trouble or have any questions about setting up RLS or permissions, feel free to share more details happy to help further.

If the response helped resolve your issue, please consider marking it as Accepted as Solution and give it a thumbs up if you found it helpful.

Best regards,
Akhil.

[Akash_Varuna]

Hi @Dimani This issue occurs because DirectQuery requires the other user to have access to the SQL Server database and the data gateway. Grant the user gateway access in Power BI Service and ensure they have database credentials. Alternatively, set up row-level security (RLS) to control data access or grant them Build permission on the dataset to allow custom reports without direct database access.

[Poojara_D12]

Hi @Dimani 

In your scenario, since the Power BI report is using DirectQuery to connect to a SQL Server via an on-premises data gateway with your credentials, another user cannot simply reuse that connection without proper access. When you send them a copy of the .pbix file, Power BI Desktop will prompt them to authenticate the data source using their own credentials—they must have access to both the SQL Server database and the gateway in order to refresh or query the data. You cannot share your credentials directly, and the gateway must be configured to allow their access through a defined data source user mapping in the Power BI Service.

If the other user only needs a subset of the data and shouldn't have full access to the SQL Server, a better approach is to publish the semantic model (dataset) yourself, apply row-level security (RLS) roles in Power BI Desktop, and configure those roles in the Power BI Service by assigning the user to appropriate roles. Then, instead of sharing the full .pbix file, you can give the user Build permission on the semantic model so they can connect to it from a blank report using “Get data > Power BI datasets,” and customize only the visuals without needing direct access to the database or gateway. This way, you keep control of the source data, ensure data access is restricted, and reduce risk, especially since the report uses DirectQuery where every user action potentially hits the underlying database.

[Dimani]

Hi Poojara_D12 and Akash_Varuna,

Hope both of you had a good weekend! Thanks to both of your replies!

@Poojara_D12 , thanks for your detail reply with highlight of the important points!

1. Open the semantic model in Power BI Desktop
2. Create a role in Power BI Desktop
3. Publish the semantic model to Power BI Service
4. Assign the user the role in Power BI Service
5. Grant the user BUILT permission in Power BI Service
   
   

There is no need to give the user permission in the connection and gateway.

Follow-up questions:

1. Does the approach above work with any storage models in Power BI Desktop?

After posting my initial questions, I found that there are two more files in which the user is interested.

One has some tables using "Direct" storage mode and the rest of the tables using "Import" mode.
The other one has all of its tables using "Import" mode.

In both files, I noticed that the "Manage roles" option is available in Power BI Desktop, a sign that your approach would work for any storage mode?

2. Do I need to create scheduled refreshes for the semantic models that have any tables using "import" storge mode?

Since I will not grant the user permission in using the connection and the data gateway, to keep the data up to date for the user, I will have to create refresh schedules?

3. Can the user publish his reports in any workspace?

The user works in a different group, can he publish his reports in another workspace (not his personal workspace) where he can create an Power BI app to share his customized reports with his teammates? 
Or he can only publish his reports in our workspace because the semantic models are in our workspace?

If the latter, I wonder if it is possible to have more than one Power BI app in the same workspace.  From my initial search, it appears it is not possible.

4. Would it be better to update the semantic model files with a role, rather than updating the files with a role and do a save as to new files? Or it depends?

It seems from the maintenance point of view, it would be less work if we simply add a role to the existing semantic model files instead of saving them to a different set of files. Wonder if there are reasons we may want to create another set of semantic model files for the user.

Thank you!