0

I know how to create a new column based on an old one , encrypt it after creating a master key/certificate and etc.

I would like to know what would be the best approach to encrypt ceartin columns without the need to create a new one.

I mean, is there a way to encrypt a column, and during a select, we see encrypt data or something like J&Y@HS@H&HS and only some users can see it?

I'm not talking about TDE in the entire database or backup encryption.

Thanks.

3
  • This could help --> sqlshack.com/… Oct 14, 2020 at 16:59
  • 1
    Thanks @Learning_DBAdmin I was looking that website right now. and it does exactly what I said, it creates a new column based in an old one.
    – Racer SQL
    Oct 14, 2020 at 17:13
  • 2
    Sounds like you're looking for data masking
    – mustaccio
    Oct 14, 2020 at 17:56

1 Answer 1

1

Dynamic Data Masking (DDM) can fulfil the requirement as long as the Least Privilege Principle followed, since by default members of sysadmin server role and members of db_owner database role can always see UNMASKED (Including Encrypted columns).

If the requirement as such that even sysadmin role members must only see the encrypted data then the better fit will be Always Encrypted feature...

Or, if the goal is just to protect some sensitive data within database when it restored into DEV/UAT environments, better look for reliable third-party solution, or you can also develop you're own stored procedure/script that can be executed (manually) in DEV DB every time a restoration happened.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.