Here's another demo I just used for a presentation of SQL Server 2005 encryption features. This shows how multiple keys can be used for encrypting data in a column and also how access to keys can be controlled. It's a fairly long demo:

--
-- Column Encryption Demo - "Clinic"
--
use master

-- Create two logins that will be used for this demo
-- They are logins for two doctors in a clinic
--
create login Doc1 with password = 'Yukon90!'
create login Doc2 with password = 'Yukon90!'

-- Create a database that the clinic uses
--
create database ClinicDB

-- Switch to the clinic database
--
use clinicDB

-- Create users for the doctors
--
create user Doc1
create user Doc2

-- Create a table for patient records
-- The SSN and health problems of each patient is meant
-- to be encrypted. Only the patient's doctor can see their
-- full details. Other doctors can see the patient's name
-- and which doctor is assigned to them
--
create table PatientTable (
 Id int,
 name nvarchar(30),
 Docname varchar(20),
 SSN varbinary(60),
 Problem varbinary(400))

-- Grant access to the table to both doctors
--
grant select, insert on PatientTable to Doc1
grant select, insert on PatientTable to Doc2

-- Create a certificate for each doctor
--
create certificate Doc1Cert
authorization Doc1 with subject = 'Doc1cert'

-- Notice the error, we need to create a database master key first.
-- So lets do that
--
create master key
ENCRYPTION BY password = 'ClinicDB1'

-- Now let's create the certificates
-- that will protect the data encryption keys
--
create certificate Doc1Cert
authorization Doc1 with subject = 'Doc1cert'

create certificate Doc2Cert
authorization Doc2 with subject = 'Doc2cert'

-- View certificates in database
--
select * from sys.certificates

-- Create symmetric keys for each of the doctors
--
create symmetric key Doc1Key
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate Doc1Cert

create symmetric key Doc2Key
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate Doc2Cert

-- Grant permissions on the symmetric keys
--
grant view definition on symmetric key::Doc1Key to Doc1
grant view definition on symmetric key::Doc2Key to Doc2

-- View keys in database
--
select * from sys.symmetric_keys

 

---------------------------------
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'

-- Note we cannot open the other doctor's key
--
open symmetric key Doc2Key
DECRYPTION BY certificate Doc2cert

-- Open symmetric key for usage
--
open symmetric key Doc1Key
DECRYPTION BY certificate Doc1cert

-- View the list of open keys in the session
--
select * from sys.openkeys

-- Use the key to insert doctor's patient records
-- including encrypted values into the table
--
insert into PatientTable values (
 1,
 'Alice',
 'Doc1',
 encryptByKey(Key_GUID('Doc1Key'), '111-11-1111'),
 encryptByKey(Key_GUID('Doc1Key'), 'Migraine'))

insert into PatientTable values (
 2,
 'Bob',
 'Doc1',
 encryptByKey(Key_GUID('Doc1Key'), '222-22-2222'),
 encryptByKey(Key_GUID('Doc1Key'), 'Stomach Ache'))

insert into PatientTable values (
 3,
 'Charles',
 'Doc1',
 encryptByKey(Key_GUID('Doc1Key'), '333-33-3333'),
 encryptByKey(Key_GUID('Doc1Key'), 'Sore throat'))

-- Close all opened keys
--
close all symmetric keys

-- View the list of open keys in the session
--
select * from sys.openkeys

-- Simulate logging out of doctor1's context
--
revert

 

---------------------------------
-- Simulate connecting as Doctor2
--
execute as login = 'Doc2'

-- Open symmetric key for usage
--
open symmetric key Doc2Key
DECRYPTION BY certificate Doc2cert

-- View the list of open keys in the session
--
select * from sys.openkeys

-- Use the key to insert doctor's patient records
-- including encrypted values into the table
--
insert into PatientTable values (
 4,
 'Dave',
 'Doc2',
 encryptByKey(Key_GUID('Doc2Key'), '444-44-4444'),
 encryptByKey(Key_GUID('Doc2Key'), 'Throat infection'))

insert into PatientTable values (
 5,
 'Emily',
 'Doc2',
 encryptByKey(Key_GUID('Doc2Key'), '555-55-5555'),
 encryptByKey(Key_GUID('Doc2Key'), 'Asthma'))

insert into PatientTable values (
 6,
 'Frank',
 'Doc2',
 encryptByKey(Key_GUID('Doc2Key'), '666-66-6666'),
 encryptByKey(Key_GUID('Doc2Key'), 'Eye Pain'))

-- Close all opened keys
--
close all symmetric keys

-- Simulate logging out of doctor2's context
--
revert

 

-- Select from table as an admin
--
select * from patientTable
--
-- Note how last two columns show up as encrypted text

 

---------------------------------
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'

-- Open the relevant key for usage
--
open symmetric key Doc1Key
DECRYPTION BY certificate Doc1Cert

-- Select from the table including decrypting from
-- encrypted columns
--
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykey(SSN)) as SSN,
 convert (varchar, decryptbykey(Problem)) as Ailment
from PatientTable

-- Simulate logging out
--
close all symmetric keys
revert

 

--------------------------------
-- Simulate connecting as Doctor2
--
execute as login = 'Doc2'

-- Open the relevant key for usage
--
open symmetric key Doc2Key
DECRYPTION BY certificate Doc2Cert

-- Select from the table including decrypting from
-- encrypted columns
--
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykey(SSN)) as SSN,
 convert (varchar, decryptbykey(Problem)) as Ailment
from PatientTable

-- Simulate logging out
--
close all symmetric keys
revert

 

-- Now let's get rid of the need to open the key
--
create view Doc1PatientView
as
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykeyautocert(cert_id('Doc1Cert'), NULL, SSN)) as SSN,
 convert (varchar, decryptbykeyautocert(cert_id('Doc1Cert'), NULL, Problem)) as Ailment
from PatientTable

create view Doc2PatientView
as
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykeyautocert(cert_id('Doc2Cert'), NULL, SSN)) as SSN,
 convert (varchar, decryptbykeyautocert(cert_id('Doc2Cert'), NULL, Problem)) as Ailment
from PatientTable

-- Grant permissions on these views
--
grant select on Doc1PatientView to Doc1
grant select on Doc2PatientView to Doc2

-- These are for demo, will not work
--
grant select on Doc1PatientView to Doc2
grant select on Doc2PatientView to Doc1

 

---------------------------------
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'

-- Note no keys are opened
--
select * from sys.openkeys

-- Select from Doc1's view
--
select * from Doc1PatientView

-- Also note, no keys are left opened
--
select * from sys.openkeys

-- Select from Doc2's view
-- will not decrypt anything
--
select * from Doc2PatientView

-- Simulate logging out
--
revert

 

---------------------------------
-- Simulate connecting as Doctor2
--
execute as login = 'Doc2'

-- Select from Doc2's view
--
select * from Doc2PatientView

-- Select from Doc1's view
-- will not decrypt anything
--
select * from Doc1PatientView

-- Simulate logging out
--
revert

 

-- What if we need to share information
-- Add an extra key that both doctors have access to
-- and encrypt data with it
--
create symmetric key DocsKey
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate Doc1Cert

open symmetric key DocsKey
DECRYPTION BY certificate Doc1Cert

alter symmetric key DocsKey
add ENCRYPTION BY certificate Doc2Cert

-- Insert a common patient record
--
insert into PatientTable values (
 7,
 'George',
 'Doc1 or Doc2',
 encryptByKey(Key_GUID('DocsKey'), '777-77-7777'),
 encryptByKey(Key_GUID('DocsKey'), 'Imaginary Disease, likes to visit our clinic'))

close symmetric key DocsKey

-- Check the encryptions for the key
-- Each one allows one doctor access to the key
--
select c.name as 'Encrypted By' from sys.key_encryptions ke, sys.certificates c, sys.symmetric_keys sk
where ke.thumbprint = c.thumbprint and sk.name = 'DocsKey' and ke.key_id = sk.symmetric_key_id;

-- Allow both doctors access to the common key
--
grant view definition on symmetric key::DocsKey to Doc1
grant view definition on symmetric key::DocsKey to Doc2

 

---------------------------------
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'

-- Select from Doc1's view
--
select * from Doc1PatientView

-- Simulate logging out
--
revert

 

---------------------------------
-- Simulate connecting as Doctor2
--
execute as login = 'Doc2'

-- Select from Doc2's view
--
select * from Doc2PatientView

-- Simulate logging out
--
revert

 

-- How can the admin get access to data without opening each key?
-- Create a master certificate that allows access to all keys
--
create certificate MasterCert
with subject = 'mastercert'

open symmetric key Doc1Key
DECRYPTION BY certificate Doc1Cert
open symmetric key Doc2Key
DECRYPTION BY certificate Doc2Cert
open symmetric key DocsKey
DECRYPTION BY certificate Doc1Cert

select * from sys.openkeys

alter symmetric key Doc1Key
add ENCRYPTION BY certificate MasterCert
alter symmetric key Doc2Key
add ENCRYPTION BY certificate MasterCert
alter symmetric key DocsKey
add ENCRYPTION BY certificate MasterCert

close all symmetric keys

select * from sys.openkeys

-- Now create a view that automatically decrypts using the master certificate
--
create view PatientView
as
select
 Id,
 name,
 Docname,
 convert (varchar, decryptbykeyautocert(cert_id('MasterCert'), NULL, SSN)) as SSN,
 convert (varchar, decryptbykeyautocert(cert_id('MasterCert'), NULL, Problem)) as Ailment
from PatientTable

select * from PatientView

-- these are for the demo; to show they're not sufficient
--
grant select on PatientView to Doc1
grant select on PatientView to Doc2

 

---------------------------------
-- Note that the doctors can't use this view
--
-- Simulate connecting as Doctor1
--
execute as login = 'Doc1'

-- Select from Doc1's view
--
select * from PatientView

-- Simulate logging out
--
revert

 

---------------------------------
-- Simulate connecting as Doctor2
--
execute as login = 'Doc2'

-- Select from Doc2's view
--
select * from PatientView

-- Simulate logging out
--
revert

 

-- Cleanup
--
use master

drop database ClinicDB
drop login Doc1
drop login Doc2
--EOD