1.首先需要在对应的库里，创建一个KEY
Use Finance
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'xxxwdwdjdhaw$lefjhes234fsfjs'
go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
go
2.加密的存储过程
CREATE PROCEDURE [dbo].[EncryptText_sp]
(
@InputText VARCHAR(50)
,@EncryptedText VARBINARY(128) OUTPUT
)
AS
BEGIN
OPEN SYMMETRIC KEY InternalSymmetricKey
DECRYPTION BY CERTIFICATE InternalCertificate
SELECT @EncryptedText = EncryptByKey(Key_GUID('InternalSymmetricKey'),@InputText)
CLOSE SYMMETRIC KEY InternalSymmetricKey
END
GO
3.解密的存储过程
CREATE PROCEDURE [dbo].[DecryptText_sp]
(
@InputText VARBINARY(128)
,@DecryptedText VARCHAR(50) OUTPUT
)
AS
BEGIN
OPEN SYMMETRIC KEY InternalSymmetricKey
DECRYPTION BY CERTIFICATE InternalCertificate;
SELECT @DecryptedText = DecryptByKey(@InputText)
CLOSE SYMMETRIC KEY InternalSymmetricKey
END
GO
也可以使用查询语句
SELECT CONVERT(VARCHAR,DecryptByKey(password)) as PassWord,
FROM User
