cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tommy_g Regular Visitor
Regular Visitor

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

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
2 REPLIES 2
ankitpatira Super Contributor
Super Contributor

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

@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 Regular Visitor
Regular Visitor

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

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.

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 432 members 4,187 guests
Please welcome our newest community members: