cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RhysGoodwin Frequent Visitor
Frequent Visitor

Columns missing with SQL Datareader Permission

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:

 

 SSMS.PNGPBIColumnMissing.PNG

SQLPermissions.PNG

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-haibl-msft
Microsoft

Re: Columns missing with SQL Datareader Permission

@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

View solution in original post

4 REPLIES 4
Moderator v-caliao-msft
Moderator

Re: Columns missing with SQL Datareader Permission

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 Frequent Visitor
Frequent Visitor

Re: Columns missing with SQL Datareader Permission

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

 

Cheers,

Rhys

 

Microsoft v-haibl-msft
Microsoft

Re: Columns missing with SQL Datareader Permission

@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

View solution in original post

RhysGoodwin Frequent Visitor
Frequent Visitor

Re: Columns missing with SQL Datareader Permission

Thanks very much Herbert. 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)