Forum Discussion

ytangbi's avatar
ytangbi
New Member
8 years ago
Solved

Columns missing with some users even after granting these users with db_owner access

I have same issue with 

 

 I have a group with datareader only  access to a SQL DB. 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. 

I read a toptic "Columns missing with SQL Datareader Permission' with same issue ( http://community.powerbi.com/t5/Desktop/Columns-missing-with-SQL-Datareader-Permission/m-p/150383#M64982)  and suggested solutions to the issue:

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.

 

I tried both and even tried to grant the group with  DBO role  but my issue was sitll not solved .

 

any idea and thought?

Thanks

 

PS: My BI desktop Version: 2.51.4885.701 64-bit (October 2017)

 

  • Hi ytangbi,

     

    Based on my test in the same desktop version, after grant the user view definition permission(use databasename; grant view definition to username), the user can view the all columns in the table. 

     

    In your scenario, please open Data source settings, check which credential you are using, then make sure you are granting the correct user. After you grant the user, please restart the Power BI desktop, then try again. 

     

     

     

    Best Regards,
    Qiuyun Yu 

  • Thank you Qiuyun!

     

    The user said he was okay now.

    The best regards,

     

    Yuqng Tang

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support

    Hi ytangbi,

     

    Based on my test in the same desktop version, after grant the user view definition permission(use databasename; grant view definition to username), the user can view the all columns in the table. 

     

    In your scenario, please open Data source settings, check which credential you are using, then make sure you are granting the correct user. After you grant the user, please restart the Power BI desktop, then try again. 

     

     

     

    Best Regards,
    Qiuyun Yu 

  • Thank you Qiuyun!

     

    The user said he was okay now.

    The best regards,

     

    Yuqng Tang

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support

    Hi ytangbi,

     

    So the issue is resolved now, right? If it is, I will close this thread. 

     

    Best Regards,
    Qiuyun Yu