Opaque Encryption - with No Views, or Special SQL

  • Hi,

    I want to encrypt certain columns in various tables in a SQL Server 2019 database.

    I would like to do this, for five groups of users - or five roles. Some of these users actually use SSMS and run queries themselves, while others access data through interactive reports, and some link to the database through Excel.

    However I would like to achieve this:

    • Without using views  [extra work, and requires users to know where to query]
    • Without users to having to write any special words in their SQL like encrypt/decrypt

    So the users, up front, have no idea/no clue that anything is encrypted. They just run their reports or run their queries like they always do, and then only if they happen to return the results with encrypted data, completely based on their role, they see the actual or they see the encrypted data.  Solely based on their role.

    The idea here is the users have to know NOTHING, or have to do any extra work in regards to knowing where or which tables/to query or what SQL to write - they just do everything as they did, just like before encryption happened.

    I don't think SQL server supports role based encryption like this - correct????




    • This topic was modified 1 month ago by  Vmusic.
  • You would do that with view(s).  The ENCRYPT / DECRYPT would be only within the view(s).

    The users don't have to query anything different, as you can make the original table name a view name (indeed, users should only ever query from a view name, never from the real table name).  SQL will treat the view the same as a table as far as queries go.

    SQL also has masking capability, if you don't really need encryption but just need a way to prevent users from seeing certain columns / data.


    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thank you!!

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply