Restritions due to SQL Select column permissions

Hi, in my MS SQL DB, user have Select permission only for some columns (like 40 of 350). So when user see some chart and click “View these xyz” it will fail when internal query reach first not-granted column, same with x-rays. Also creating questions is harder, because all columns are offered (but not granted).

I know that I can set “Do not include” visibility in data model, but I really don’t want do it manually for 310 columns…
Any workaround for this ?
And is it possible disable “View these” and “x-ray” for particular DB only ?

Hi @CZvacko
Just to understand you fully, you have a database-user, where you’ve defined restricted privileges for some columns in some of your tables?

I don’t understand how some of these columns are presented to the end-user with an option to drill-through, which then leads to query errors.

Since I don’t quite understand this, then my recommendation is to create database views, so Metabase has full access to “tables” that have already been restricted.

There’s no option to disable drill-through (that’s a core Metabase functionality) unless you remove the users data-access in Admin > Permissions, and only give them view-access to collection, but then the user can only view questions.

There’s no option to disable X-rays for specific databases, only for all via Admin > Settings > General.

Yes, like this. Currently I present only “granted” columns, but drill-through want to query also “not-granted” columns. Database views might be solution, but that will also require many manual work, expecially when grant another column in future. It can be nice if Metabase can verify column restiction when adding new DB.

@CZvacko It’s a bit more complicated, since Metabase has to work across many databases. But it might be possible, if this gets merged:
https://github.com/metabase/metabase/pull/10892
Until then, your best option would be a view.