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,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. 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.


  2. Erland Sommarskog 100.1K Reputation points MVP
    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 No comments

  3. Olaf Helper 40,156 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 No comments

  4. Seeya Xi-MSFT 16,426 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 No comments