Allow user to see SQL view only when connecting PBI Desktop to SQL Server

[DaveKoni]

We have an on premise SQL Server. It hosts our MAIN database as well as a REPORT database. The REPORT database has sql views built in it, these views use data from the MAIN db. I am struggling to figure out how to grant a user access to the view only.

If I give a user (or a security group they are a part of) SELECT on the view, when the user connects via PBI Desktop to the SQL server they can select the view but get the error that "The SELECT permission was denied on the object 'Organization', database "MAIN", schema 'dbo'

If I grant the user SELECT on the MAIN db then when they connect to the SQL server with PBI Desktop they then have access to the MAIN db and this defeats the goal of just giving them access to views.

Ideally I would like the user to only see and use the views that we select for them in PBI Desktop.  

[v-cazheng-msft · Accepted solution]

Hi @DaveKoni 

Since your data is in the MAIN database, you need to at least give the user the read permission of the table from which the data is stored. You can consider mapping users to make users indirectly have access to the data. You can refer this article Grant table-level permissions in SQL Server, it may help you.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.