USE family;
GO

GO

-- 2. Create a Certificate
CREATE CERTIFICATE CashEncryptionCert
WITH SUBJECT = 'Certificate for encrypting cash table';
GO

-- 3. Create a Symmetric Key
CREATE SYMMETRIC KEY CashSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE CashEncryptionCert;
GO

-- 4. Add Encrypted Column (optional)
ALTER TABLE cash ADD amount_encrypted VARBINARY(MAX);
GO

-- 5. Encrypt Existing Data
OPEN SYMMETRIC KEY CashSymmetricKey
DECRYPTION BY CERTIFICATE CashEncryptionCert;

-- 6. (Optional) Remove or Obfuscate Original Column
UPDATE cash
SET amount_encrypted = ENCRYPTBYKEY(KEY_GUID('CashSymmetricKey'), CAST(amount AS VARCHAR(50)));

CLOSE SYMMETRIC KEY CashSymmetricKey;
GO

