-- Script originally written as examples from SQL Team
-- Modifications made to show backup/restore across two instances

USE master
--Create two logins that will be used for this demo
--Essentially logins for two doctors in a clinic
CREATE LOGIN Doc1 WITH PASSWORD = 'Yukon90!05'
go
CREATE LOGIN Doc2 WITH PASSWORD = 'Yukon90!05'
go

--Create a database that the clinic uses
CREATE DATABASE ClinicDB
go
--switch to the clinic database
USE clinicDB
go
--create users for to the doctors
CREATE USER Doc1
go
CREATE USER Doc2
go

--create a table for patient records
--The SSN and health problems of each patient is meant
--to be encrypted. Only the patients doctor can see their
--full details. Other doctors can see the patients name
--and which doctor is assigned to them
CREATE TABLE PatientTable 
(	Id int
	, name nvarchar(30)
	, Docname varchar(20)
	, SSN varbinary(60)
	, Problem varbinary(4000)
)
go
--grant access to the table to both doctors
GRANT SELECT,INSERT ON PatientTable TO Doc1
GRANT SELECT,INSERT ON PatientTable TO Doc2

--Now to all the encryption stuff.
--Create a certificate for each doctor
CREATE CERTIFICATE Doc1Cert 
AUTHORIZATION Doc1 WITH subject = 'Doc1cert'
go
--Notice the error, we need to create a database master key first. 
--So lets do that.
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'ClinicDB1'

--Now lets create the certificates
CREATE CERTIFICATE Doc1Cert 
AUTHORIZATION Doc1 WITH subject = 'Doc1cert'
go

CREATE CERTIFICATE Doc2Cert 
AUTHORIZATION Doc2 WITH subject = 'Doc2cert'
go

--create symmetric keys for each of the doctors
CREATE SYMMETRIC KEY Doc1Key 
AUTHORIZATION Doc1 
WITH ALGORITHM = TRIPLE_DES 
ENCRYPTION BY CERTIFICATE Doc1Cert
go

CREATE SYMMETRIC KEY Doc2Key 
AUTHORIZATION Doc2 
WITH ALGORITHM = TRIPLE_DES 
ENCRYPTION BY CERTIFICATE Doc2Cert
go
--View the list of keys in the database
SELECT * FROM sys.symmetric_keys

--Simulate connecting as Doctor1
EXECUTE AS LOGIN = 'Doc1'
--open your symmetric key for usage
OPEN SYMMETRIC KEY Doc1Key DECRYPTION BY CERTIFICATE Doc1cert;

--view the list of open keys in the session
--select * from sys.dm_exec_open_keys

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

INSERT INTO PatientTable VALUES 
	(2, 'Tanmoy','Doc1'
		, encryptByKey(Key_GUID('Doc1Key'),'222-22-2222')
		, encryptByKey(Key_GUID('Doc1Key'),'Stomach Ache')
	)

INSERT INTO PatientTable VALUES 
	(3, 'James','Doc1'
		, encryptByKey(Key_GUID('Doc1Key'),'333-33-3333')
		, encryptByKey(Key_GUID('Doc1Key'),'Insanity')
	)

--close all opened keys
CLOSE ALL SYMMETRIC KEYS

--simulate logging out of doctor1's context
REVERT

--Simulate connecting as Doctor2
EXECUTE AS LOGIN = 'Doc2'
--open your symmetric key for usage
OPEN SYMMETRIC KEY Doc2Key DECRYPTION BY CERTIFICATE Doc2cert
--view the list of open keys in the session
--select * from sys.dm_exec_open_keys

--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, 'Istvan','Doc2'
		, encryptByKey(Key_GUID('Doc2Key'),'555-55-5555')
		, encryptByKey(Key_GUID('Doc2Key'),'Asthma')
	)

INSERT INTO PatientTable VALUES 
	(6, 'Al','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 doctor1's context
REVERT

--select from table as an admin
SELECT * FROM PatientTable

--notice how last two columns show up as encrypted text

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1'

--open the relevant keys 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 Doc1
EXECUTE AS LOGIN = 'Doc2'
--open the relevant keys 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 backup/restore on same server to same location, etc.
USE master
go
BACKUP DATABASE clinicDB 
	TO DISK = 'c:\SQLAvail\clinic.bkp'
	WITH INIT
go

RESTORE DATABASE clinicDB 
	FROM DISK = 'c:\SQLAvail\clinic.bkp'
	WITH REPLACE
go

USE ClinicDB
go

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1'
--open the relevant keys 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

-- This works. Next, try another instance.
-- Use second script: RestoreEncryptedDatabase.sql
