when to open and close symmetric keys I am adding column encryption to a few tables using symmetric key encryption. In each one of the procs, I write code to open and close a keys. It seems a bit verbose to open and close them in each proc. (example below )

Can I leave a key open permanently?
MSDN says keys close when a session ends. Is it possible to open a key at the beginning of a session and leave it open for all queries?

CREATE PROCEDURE [dbo].[person_sel]
AS
BEGIN

open symmetric key demoKey decryption by certificate demoCert;

select 
    personId,
    name,
    email,
    cast( DECRYPTBYKEY( pin_enc) as varchar(MAX)) as pin
     from person;

close SYMMETRIC KEY demoKey;
END
Yes.  Create (database) MASTER KEY first, then  BACKUP, DROP, and CREATE the certificate (importing from earlier BACKUP), but this time WITHOUT a password.  This will encrypt the certificate's private key for the certificate using the database master key, which will automatically open the private key as needed.

Realize by doing this, you are exposing your data (via the private key for the cert) to anyone that has access to the database and sysadmin to the server.  In reality, all you have done is kept the data-at-rest encrypted on disk.  Anyone with access to your backups and the service master key can decrypt your data.

Make sure you follow best practices for keys (and certificates) in SQL and backup the database master key.

You can view the private key encryption type by looking at the pvt_key_encryption_type_desc column in sys.certificates database dmv.