SQL SERVER 2005 builtin Encryption

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SecretTable_SecretData_Key'

GO

CREATE CERTIFICATE SecretTable_SecretData_Key

      WITH SUBJECT = 'IWMI',

      START_DATE = '01/04/2008',

      EXPIRY_DATE = '10/31/2050'

CREATE SYMMETRIC KEY SecretTable_SecretData_Key  

    WITH ALGORITHM = TRIPLE_DES  

    ENCRYPTION BY CERTIFICATE SecretTable_SecretData_Key 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'SecretTable_SecretData_Key';

 GO

  

ALTER TABLE <TABLE NAME>

ALTER COLUMN <COLUMN NAME> [varchar](400)

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SecretTable_SecretData_Key'

  

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

  

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

OPEN SYMMETRIC KEY  SecretTable_SecretData_Key  DECRYPTION BY CERTIFICATE SecretTable_SecretData_Key

declare @KeyGuid AS UNIQUEIDENTIFIER

SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')

declare @vartest as nvarchar(400) 

SELECT convert(varchar(max), decryptbykeyautocert(cert_id('SecretTable_SecretData_Key'), NULL, EBSAL_BASIC_SALARY))   FROM HS_HR_EMP_BASICSALARY

CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]