Share via

off the shelf column level security

db042190 1,516 Reputation points
2022-05-18T12:23:52.757+00:00

hi we run 2014 enterprise right now. We hope to be on 2019 in the coming months.

We are contemplating the addition of a column called "restricted access" to our dim account table. But even if an account has this flag turned on, it doesnt mean that users shouldnt be able to see the acct info. They should just be prevented from knowing its restricted. I know how weird that sounds.

Is there an off the shelf way in 2014 or 2019 to hide this column value from folks without special permissions? even in more mainstream scenarios, what if this was an employee record but we'd want to hide just the salary from folks not needing to know that info?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
0 comments

6 answers

Sort by: Newest
Most helpful Newest Oldest
  1. db042190 1,516 Reputation points
    2022-05-19T11:29:57.747+00:00

    thx all. the reality is that with self service bi, people are going to get access to tables and views.

    its funny because column level security is so much easier in tabular cubes. But i cant justify taking our org in that direction for just this reason.

    i think i like the "sensitive" table approach. I can put as many layers of security as i want on something like that. one of our cm guys even wants a separate account for accessing this part of the system. And a separate ssrs shared data source. and a separate ssrs folder. I'm guessing he wants pinch points where he can shut down misuse more easily. sounds a bit like overkill but not worth debating in my mind.

    I'm guessing that erland says i might regret the grant approach because maintaining such a thing could get unwieldy.

    0 comments
  2. Seeya Xi-MSFT 16,451 Reputation points
    2022-05-19T09:38:36.79+00:00

    Hi @db042190 ,

    Welcome to Microsoft Q&A!
    Here is an example for your inference: https://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  3. Olaf Helper 41,331 Reputation points
    2022-05-19T06:20:50.7+00:00

    new column based on permissions

    Create views fo each permission group which returns only that columns the user group are allowed to see.

    0 comments
  4. Erland Sommarskog 102.3K Reputation points
    2022-05-18T21:26:06.057+00:00

    You can grant permissions per column: 

    GRANT SELECT ON Accounts(col1, col2, selectedColumns) TO MostPeople
GRANT SELECT ON Accounts TO ThoseWhoMaySeeAll

    But you may regret it.

    I would be more inclined to solve this with a view that includes all columns but the secret one.

    0 comments
  5. db042190 1,516 Reputation points
    2022-05-18T15:52:17.033+00:00

    thx Tom, i didnt even think about obfuscation etc.

    But i think what i'm looking for is a feature that totally hides the existence of the new column based on permissions. But allows it to remain on the most appropriate table otherwise.