Best way to encrypt some selected rows

Question

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.

Answer 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.