--创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='passW@ord'
GO
--创建证书
CREATE CERTIFICATE TestCert with SUBJECT = 'Test Certificate'
GO
--创建对称密钥
CREATE SYMMETRIC KEY TestSymmetric WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert
GO
--使用对称秘钥加密数据
OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
INSERT INTO TBLUser values('张三', ENCRYPTBYKEY(Key_Guid(N'TestSymmetric'), '123456'));
CLOSE SYMMETRIC KEY TestSymmetric;
GO
--查看加密后的数据
SELECT * FROM TBLUser
GO
--解密数据
OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;
SELECT Name, CAST(DecryptByKey(password) as varchar(100)) Password FROM TBLUser;
CLOSE SYMMETRIC KEY TestSymmetric;
GO