Column level encryption in SQL server 2008r2

USE YourDB
GO

--get the list of keys in current adtabase
select * from sys.symmetric_keys

--get the list of certificates in current database
select * from sys.certificates


--To store encrypted data in the table you have to change the datatype to varbinary(256)


--set the database level encryption password
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '343k6WJussssszurWi'
GO

--create a certificate by which we are going to encrypt or decrypt data later
CREATE CERTIFICATE EncryptTESTCert
WITH SUBJECT = 'EncryptTESTCert'
GO

--create symmetric key by using certificate
CREATE SYMMETRIC KEY TESTTableKey
WITH ALGORITHM = AES_256 ENCRYPTION
BY CERTIFICATE EncryptTESTCert
GO



--EXAMPLE

--Open encryption
OPEN SYMMETRIC KEY TESTTableKey DECRYPTION
BY CERTIFICATE EncryptTESTCert

--encrypt data the show that
DECLARE @ResultVarBinary varbinary(256)              
SET @ResultVarBinary = ENCRYPTBYKEY(KEY_GUID('TESTTableKey'),'test')  
select @ResultVarBinary

--decrypt data then show that
DECLARE @ResultSTring varchar(max)
SET @ResultSTring = CONVERT(VARCHAR(max),DECRYPTBYKEY(@ResultVarBinary))
select @ResultSTring

--close current encryption
CLOSE SYMMETRIC KEY TESTTableKey


--NOTE
--Use ENCRYPTBYKEY while You are inserting data
--Use DECRYPTBYKEY When you are fetching data from table


Ref:
http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/


To use it in a easier way I have created separate SP and functions:-


ALTER procedure [dbo].[OpenEncryption]
AS
BEGIN
    OPEN SYMMETRIC KEY TESTTableKey DECRYPTION
    BY CERTIFICATE EncryptPCCASCert
END



ALTER procedure [dbo].[CloseEncryption]
AS
BEGIN
    CLOSE SYMMETRIC KEY TESTTableKey
END



ALTER FUNCTION [dbo].[EncryptData]
(
    -- Add the parameters for the function here
    @text varchar(max)
)
RETURNS varbinary(256)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar varbinary(256)       
       
    SET @ResultVar = ENCRYPTBYKEY(KEY_GUID('TESTTableKey'),@text)   

    -- Return the result of the function
    RETURN @ResultVar

END



ALTER FUNCTION [dbo].[DecryptData]
(
    -- Add the parameters for the function here
    @text varbinary(256)
)
RETURNS varchar(max)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar varchar(max)       
       
    if @text is null
        begin
            SET @ResultVar =''
        end
    else
        begin
            SET @ResultVar = CONVERT(varchar(max),DECRYPTBYKEY(@text))
        end

    if @ResultVar is null
    BEGIN
        SET @ResultVar = ''
    END
    -- Return the result of the function
    RETURN @ResultVar

END