Proper setup of gateway refering to user

[NovaBI]

Hi all,

call for best practice experience:

If we manage multiple data sources, what is the proper way when setting up a datasource/gateway connection.

Lets say we have a SQL db with multiple users, different privileges, e.g.

Admin

DB-Owner

Read-Only on 2 tables

Read-Only on 1 table

Read-Only on 1 view

If i want to set up a connection in the gateway, what is the right user to use? I dont think it is right to set up a new connection and add that to the gateway for every distinct user group. But somehow i do not like to add the admin into that connection as well.

Does it make sense to create a service user on a global db level? 

[ibarrau]

Hi Nova. Just to be clear. The user you pick for the gateway is just a user that would look for tables that were previously developed by an analyst. You can actually query or do something to the database with the gateway connection.

First someone have built a PowerBi Desktop file with X credentials. They connect to tables or views and publish to service. The Power Bi Service won't update untill a gateway is configure (if the sql server is onpremise). The connection and the user you configure in there should have the permissions to get all the tables used on the developments. You can hear they use admins because it's easier, but if you really know the tables the developers should query, then use a user that only has access to that. Other tables might fail and you will get emails but the security is properly built. All users connected to power bi will connect with that users and it will depend on the report to know which data can a user view.
It does make sense to have a service user for this.

I hope that make sense.

[NovaBI]

@ibarrau Thx for the answer. Hope i understand it right, not sure tbh.

We have multiple report creators per department, so creating a gatewayconnection that is tailor-made for every user would be an insane amount of maintenance and work. Thats not what a gateway is supposed to be for my understanding. 

To make it simple: Is it a proper approach to creata a read-only user that can query all dbs and all schemes on the on-premise SQL Server e.g..

It would cover every potential scenario when people want to use a scheduled refresh.

I know the tables the peeps are using, but it highly diverse and complex, so a jack-of-all-trades service user is the way to go, or do i oversee sth.?  

[ibarrau · Accepted solution]

Sure. I have seen that solution many times at different scenarios. Try to cover the most you can with a single read only user. That is ok.

Regards,