CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

-- Create a Temp Table
if object_id('tempdb.dbo.#Temp4')> 0  
    DROP table #Temp4

CREATE TABLE #Temp4
(ID   INT PRIMARY KEY,
 edisplayname   NVARCHAR(200),
 eusername  NVARCHAR(200),
 displayname   NVARCHAR(200),
 username  NVARCHAR(200));

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate4
   WITH SUBJECT = 'Adventureworks Test Certificate',
   EXPIRY_DATE = '10/31/2009';

if object_id('UserList') > 0 
    DROP table UserList
GO
CREATE TABLE UserList (id int not null identity(1,1), displayname sysname, username sysname, edisplayname nvarchar(255), eusername nvarchar(255))
GO
INSERT INTO UserList (displayname, username) values ('master', 'Петров')
INSERT INTO UserList (displayname, username) values ('slave', 'Иванов')

-- EncryptByCert demonstration encrypts 100 names from the Person.Contact table
INSERT
INTO #Temp4(ID, edisplayname, eusername)
SELECT ID, EncryptByCert(Cert_ID('TestCertificate4'), displayname),
    EncryptByCert(Cert_ID('TestCertificate4'), username)
FROM UserList

-- DecryptByCert demonstration decrypts the previously encrypted data
UPDATE #Temp4
SET displayname = DecryptByCert(Cert_ID('TestCertificate4'), edisplayname),
    username = DecryptByCert(Cert_ID('TestCertificate4'), eusername)

-- View the results
SELECT *
FROM #Temp4;

-- Clean up work:  drop temp table, test certificate and master key
DROP TABLE #Temp4;

DROP CERTIFICATE TestCertificate4;

DROP MASTER KEY;