This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 39%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Per this website:
The ability to hide columns is not intended to be used for data security, only to simplify and shorten the list of columns visible in the model designer or reports. To secure data, you can define security roles. Roles can limit viewable metadata and data to only those objects defined in the role. For more information, see Roles.
Perspectives I could see not being security -since one can connect to any perspective.
But say our Timesheet table - if the Employee column were hidden, then it wouldn't be personally identifiable, thus we'd achieve our security objectives (or so my initial take goes).
One could still see the column if they connected to the Cube via SSMS as an admin, but admins are fine to see everything.
Why wouldn't that be security? I could certainly add a column level security to every role, hiding that column. But what would be the difference security-wise?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 46%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJT%3C/text%3E%3C/svg%3E)
Hiding columns is just for making them not visible via the front end tools. They could still be queried in MDX or DAX with no restriction.
Securing their use via Roles would mean they could not be accessed at all.
They could still be queried in MDX or DAX with no restriction.
But unless one has admin rights, they can't see anything in SSMS when connecting to the Cube, and can't query anything at all that way.
What am I missing?
How will your end users be accessing the data? from SSMS?
Non-admin users will still be able to create a query in SSMS against the model if they have access to it. And from there they would be able to query the hidden columns data.
https://learn.microsoft.com/en-us/analysis-services/tabular-models/column-properties-ssas-tabular?view=asallproducts-allversions
"If you hide a column from the reporting client, the field is not suppressed in the model data. The field is still visible if you create a query against the model. A hidden column can still be used for grouping or sorting."
Users are viewing the data in Power BI.
For my assertion that they can't connect in SSMS, unless they're admins, I'm operating off of this.
Read: Members are allowed to query data (based on row filters) but cannot see the model database in SSMS, cannot make any changes to the model database schema, and the user cannot process the model.
All AAS roles have that same caveat listed, except for Administrator. Which matches my testing.
?
Yes, this is true. The user won't be able to see the model if they connect to the instance but they should be able to connect to the model they have access to via a query window in SSMS.
Once in this query they will be able to use the hidden columns.
they should be able to connect to the model they have access to via a query window in SSMS
That's what I was missing (probably because I don't know how to do that myself). Thanks!