locked
How to implement Column level security in Tabular Cube for SQL Server 2016 and belowi.e. model 1200 and below? RRS feed

  • Question

  • I want to implement column level security in a tabular cube.I am using SQL Server 2016. I understand that column level security works for Sql Server 2017 (i.e. model 1400) and above and row level security works very well in SSAS tabular cube. I want to implement column level security for a client requirement. 

    Please find the below scenario for more details.

    For Eg:

    Say we have 2 tables. (1 Dimension, 1 Fact) with Columns

    DimPerson  -->  (PersonId, Name, Designation, Salary, Location , Phonenumber)

    DimSalary  --> (PersonId, Salary,LastSalaryWithdrawn)

    I have 3 separate roles for the above model.

    1. Full Access.

    2. ExecutiveRole

    3. StaffRole.

    For 1(Full Access) I should be able to see entire data for all columns .

    For 2(Executive Role) I should be able to see the data for Person Name, Designation and Salary. (thereby it should hide columns like, Phone number, Location from the client tools whether it is excel or power bi)

    For 3(StaffRole) I should be able to see the  Person Name, Designation,LastDrawnSalary,PhoneNumber, Location(thereby it should hide columns like Salary)

    Our objective is to hide the column names based on different roles we have. 

    Friday, December 8, 2017 12:48 PM

Answers

All replies

  • There is currently no way in Tabular to do this.

    You could create 3 separate Models with the appropriate tables and columns, but that would be a lot of work unless you could automate it (maybe using BIML)

    Thomas


    Thomas LeBlanc twitter ( @TheSmilingDBA )

    Friday, December 8, 2017 3:40 PM
  • I have a blog post with a hacky work around, but it's all theory. I have not used this in a production model myself.

    http://geekswithblogs.net/darrengosbell/archive/2014/04/22/implementing-column-security-with-ssas-tabular-and-dax.aspx

     But your requirement is a good case for upgrading to SSAS 2017.

    http://darren.gosbell.com - please mark correct answers

    Sunday, December 10, 2017 8:04 PM
  • Hi Sudipto Duyari,

    Thanks for your question.

    I agree with Thomas. Column level security does not support for SQL Server 2016 or any lower SQL Server version. I would recommend you to upgrade to SQL Server 2017 (i.e. model 1400) which does support Object Level Security to control access to tables and/or columns for a given role.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 11, 2017 2:13 AM
  • I am unable to get the relationship to work correctly for this column security solution.  The role with no access to the sensitive data table isn't able to see any data from other tables as well.  I need to get to to be like a left join.  Using SSAS 1200
    Thursday, August 22, 2019 9:01 PM