If I use ENCRYPTBYPASSPHRASE to encrypt the full table then how can I decrypt full table easily by using DECRYPTBYPASSPHRASE?

Actually I have to use DECRYPTBYPASSPHRASE ('passphrase', column_name) for every column to decrypt it. So it becomes so lengthy command. If I want to decrypt all columns then I have to specify this statement for all columns. Instead of that, can I use DECRYPTBYPASSPHRASE in queries so that I can use it only once and it decrypts all the columns?

1 Answer 1


No. Scalar operators cannot magically transform themselves into relational operators. You use the ENCRYPTBYPASSPHRASE to encrypt a column (there is no way to 'encrypt a table') and you use DECRYPTBYPASSPHRASE to decrypt a column.

Using passphrases to encrypt the data directly is a very very poor choice. A proper key hierarchy is needed where data is encrypted with a set of symmetric keys and the symmetric keys are in turn encrypted with a certificate. Encrypting the data directly with a passphrase requires expensive size-of-data operation to rotate the keys.

Your Answer

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

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