Aaron Morton has a very interesting post and demo that show how MARS can be used to access keys temporarily opened by a procedure. This is a must-read for anyone that is interested in implementing custom restrictions around the use of encryption keys. Some time ago, I wrote a post about restricting the use of an encryption key just for encryption. Aaron's demo goes further and demonstrates a pitfall if one would attempt to restrict the use of an encryption key for specific data decryption. The issue happens because MARS allows interleaving around SELECT statements, and a way to prevent it is to use auto-decryption routines, which open the key in the context of the transaction rather than the session context.