Columns missing with SQL Datareader Permission

[RhysGoodwin]

Hi,

I have an SQL DB. I've given a group datareader access. When the user uses SSMS they can see all the columns in the product table but when they connect via Power BI Desktop some columns are missing. In particular the name column:

 

As soon as I give the group db_owner access and refresh the preview in the query editor the 'name' column appears!

PBI Desktop Version is  2.44.4675.521 64-bit (March/2017)

Any thoughts would be much appreciated.

Cheers,

Rhys

[v-haibl-msft · Accepted solution]

@RhysGoodwin

Please refer to the response from Product Team as below.

The reason for this is that the "Name" column has a user-defined type "Name". We currently try to read user-defined type data from sys.types and when that fails, the column gets excluded. We've filed bug to look into relaxing this requirement.

Meanwhile, there are two workarounds possible:

1) For every user-defined type that needs to be visible by this user, grant "view definition" on that type with "grant view definition on TYPE::typename to username"

2) Grant the user "view definition" on the entire database with "use databasename; grant view definition to username"

The first might be a little tedious, but the second might be a little too broad.

Best Regards,
Herbert

[RhysGoodwin]

Thanks very much Herbert. 

[v-caliao-msft]

Hi @RhysGoodwin,

I have tested it on my local environemnt, we can reproduce this issue. I will report this issue internally, and post here if there are any updates.

Besides, based on my tested, the issue only occurs on DirectQuery. To workaround this issue, you can use import mode to import the data into Power BI.

Regards,

Charlie Liao

[RhysGoodwin]

Thanks Charlie. Good to know it's not just me.

Cheers,

Rhys