SQL Server encryption and decryption data with keys and certificate

create database mydb

-----------------------------------------------------------------------------

CREATE TABLE Employee

(Emp_ID int NOT NULL,

Emp_Date datetime DEFAULT GETDATE() NOT NULL,

Emp_Comments varchar(2000) NOT NULL)

-----------------------------------------------------------------------------

USE [master]

GO

CREATE LOGIN [User1] WITH PASSWORD=N'Pa$$w0rd'

GO

USE [mydb]

GO

CREATE USER [User1] FOR LOGIN [User1]

GO

-----------------------------------------------------------------------------

GRANT SELECT, INSERT, UPDATE ON Employee

TO User1

-- Create database master key

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- Create certificate

CREATE CERTIFICATE MyCert

AUTHORIZATION User1

WITH SUBJECT = 'test certificate'

GO

-- Create symmetric key

CREATE SYMMETRIC KEY MyKey

AUTHORIZATION User1

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE myCert

GO

-- Insert encrypted data

EXECUTE AS USER = 'User1'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert

INSERT INTO Employee

VALUES

(1, DEFAULT, EncryptByKey(Key_GUID('MyKey'),'Employee comment data'))

CLOSE ALL SYMMETRIC KEYS

REVERT

-- Read the decrypted data

EXECUTE AS USER = 'User1'

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert

SELECT CONVERT(varchar,DecryptByKey(Emp_Comments)) AS Comments FROM Employee

CLOSE ALL SYMMETRIC KEYS

REVERT