Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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