-- This works. Go to another instance on the same machine and try second script...
USE master
go
--You need the logins first... 
CREATE LOGIN Doc1 WITH PASSWORD = 'Yukon90!05'
go
CREATE LOGIN Doc2 WITH PASSWORD = 'Yukon90!05'
go

RESTORE DATABASE clinicDB 
	FROM DISK = 'c:\SQLTemp\clinic.bkp'
	WITH MOVE N'ClinicDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\ClinicDBData.mdf'
		, MOVE N'ClinicDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\ClinicDBLog.LDF'
go

USE ClinicDB
go
--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1'

-- FAILS WITH 
-- Msg 916, Level 14, State 1, Line 1
-- Server principal 'Doc1' is not able to access database 'clinicDB' under the current security context.

EXECUTE sp_change_users_login  'Update_One', 'Doc1', 'Doc1'
EXECUTE sp_change_users_login  'Update_One', 'Doc2', 'Doc2'
-- to match up all of the SQL Auth users...
go

-- try again...
--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1' -- we get past the previous error
--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc1Key USING CERTIFICATE Doc1Cert
-- Generates: Msg 15581, Level 16, State 1, Line 1
-- Please create a master key in the database or open the master key in the session before performing this operation.
REVERT

-- So, I think that maybe that will help...
USE master
go

CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'ClinicDB1'
go

USE ClinicDB
go

--simulate connecting as Doc1
execute as login = 'Doc1' -- we get past the previous error
--open the relevant keys for usage
open symmetric key Doc1Key using certificate Doc1Cert
-- Same error
-- Msg 15581, Level 16, State 1, Line 1
-- Please create a master key in the database or open the master key in the session before performing this operation.

CLOSE ALL SYMMETRIC KEYS
REVERT

-- Really, what we need to do is:
USE clinicDB
go
OPEN MASTER KEY USING PASSWORD = 'ClinicDB1'
go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
go

--simulate connecting as Doc1
EXECUTE AS LOGIN = 'Doc1'
select user_name(), current_user
--open the relevant keys for usage
OPEN SYMMETRIC KEY Doc1Key USING 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 USING 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

-- Everything should work again!