I would like to hide Azure DB tables from Get Data, allowing access only to selected views

[tommy_g]

We have an Azure SQL Database with 60 tables and 30 views which we want to HIDE from users when they connect.

We want them to only be able to select data which is prepared and presented as views, mostly denormalized and shaped for optimal analytics.

On my development instance of SQL Server 2016, I created a role for our users which added an explicit DENY on table and view selects.

Great! They are no longer visible in PBI Desktop, leaving only the intended views.

However, when we attempt the same action on the production database, which is Azure SQL DB v12, the restricted tables and view are listed. When selecting those tables and views, we get "Permission denied" error message, which is true, but hardly the kind of response we would want to present to our users.

I am not sure whether this is an Azure or Power BI issue, but surely not unique to our application.

Desired end state: present user with a clean, filtered (curated) list of views and eliminate all the underlying tables.

SSAS is not in play, this app is more like a Data Vault.

DataSource.Error: Microsoft SQL: The SELECT permission was denied on the object 'Analysis', database 'bear_db', schema 'dbo'.
Details:
    DataSourceKind=SQL
    DataSourcePath=xxxx.database.windows.net;bear_db
    Message=The SELECT permission was denied on the object 'Analysis', database 'xxxx', schema 'dbo'.
    Number=229
    Class=14

[ankitpatira]

@tommy_g I am not sure if there is a way in Azure SQL DB to do exactly like you did in SQL Server 2016 but from Power BI perspective I can think of a way as below to achieve what you want.

I would first of all not give access to Azure SQL DB to any of the users. Then I would connect to Azure SQL DB in power bi service and only connec to Views created and then publish that as an organisational content pack to entire organisation. Meaning any power bi user would now only make use of that organisational content pack to build reports / dashboards and they don't need to connect to data source which is Azure SQL DB. As an owner of content pack it would be my responsibility to keep that dataset updated but since it is Azure SQL DB it would be handled automatically by Power BI.

[tommy_g]

Ankit, thanks for one potential workaround. The problem is that it is more complicated than direct access, and the client will not accept a solution that does not work with live data. If this were a traditional DW situation the time lag would be expected.

Upon further research I have verified that technique of denying Select permissions on the restricted tables actually does work in SQL Server 2016.

Behavior is the same across Power BI Desktop, Access 2016 and Excel 2016: restricted tables & views are never listed to the end-user.

I'm hoping there is another trick I can do on the Azure DB side that will allow me to filter tables & views in PBI effectively.