Cacher is the code snippet organizer for pro developers

--https://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
----------------------Step 1 - Create a sample SQL Server table----------------------
USE encrypt_test;
GO

-- Create Table
CREATE TABLE dbo.Customer_data (
	Customer_id INT CONSTRAINT Pkey3 PRIMARY KEY NOT NULL
	,Customer_Name VARCHAR(100) NOT NULL
	,Credit_card_number VARCHAR(25) NOT NULL
	)

-- Populate Table
INSERT INTO dbo.Customer_data
VALUES (
	74112
	,'MSSQLTips2'
	,'2147-4574-8475'
	)
GO

INSERT INTO dbo.Customer_data
VALUES (
	74113
	,'MSSQLTips3'
	,'4574-8475-2147'
	)
GO

INSERT INTO dbo.Customer_data
VALUES (
	74114
	,'MSSQLTips4'
	,'2147-8475-4574'
	)
GO

INSERT INTO dbo.Customer_data
VALUES (
	74115
	,'MSSQLTips5'
	,'2157-1544-8875'
	)
GO

-- Verify data
SELECT *
FROM dbo.Customer_data
GO

----------------------Step 2 - SQL Server Service Master Key----------------------
USE master;
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';
GO


----------------------Step 3 - SQL Server Database Master Key----------------------
-- Create database Key
USE encrypt_test;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO


----------------------Step 4 - Create a Self Signed SQL Server Certificate:----------------------
-- Create self signed certificate
USE encrypt_test;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

----------------------Step 5 - SQL Server Symmetric Key----------------------
-- Create symmetric Key
USE encrypt_test;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
 WITH ALGORITHM = AES_128 
 ENCRYPTION BY CERTIFICATE Certificate1;
GO

--Step 6 - Schema changes
USE encrypt_test;
GO
ALTER TABLE Customer_data 
ADD Credit_card_number_encrypt varbinary(MAX) NULL
GO


----------------------Step 7 - Encrypting the newly created column----------------------
-- Populating encrypted data into new column
USE encrypt_test;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE Customer_data
SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM dbo.Customer_data;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

----------------------Step 8 - Remove old column----------------------
USE encrypt_test;
GO
ALTER TABLE Customer_data
DROP COLUMN Credit_card_number;
GO

----------------------Step 9 - Reading the SQL Server Encrypted Data----------------------
USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
 
 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

----------------------Step 10 - Adding Records to the Table----------------------
USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
-- Performs the update of the record
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) );    
GO

----------------------Step 11 - Accessing the Encrypted Data----------------------
Execute as user='test'
GO
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

Execute as user='guest'
GO
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

----------------------Step 12 - Grant Permissions to the Encrypted Data----------------------
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test; 
GO
GRANT VIEW DEFINITION ON Certificate::Certificate1 TO test;
GO