以下範例建立一個具有儲存加密，讀取解密的資料表
參考網址：
http://dotnetframework.blogspot.tw/2011/01/blog-post.html
http://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10559.aspx
1.建立非對稱金鑰。指定name=mykey, pwd=p@ssw0rd
CREATE ASYMMETRIC KEY mykey
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO
2.查詢第1步驟所建立的key是否成功。
select * from sys.asymmetric_keys
3.建立測試資料表
4.建立新增、更新、刪除資料表的預存程序。
USE [EIP_new]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_BPM_Modify_t_BPM_Seal_type_E]
@action_type NCHAR(1) = '' ,
@id INT = 0,
@username NVARCHAR(50) = '',
@uid NVARCHAR(10) = '',
@pwd NVARCHAR(30) = '',
@sort INT = 0,
@cdt DateTime
AS
BEGIN
SET NOCOUNT ON;
--傳入資料檢核
IF (@action_type NOT IN ('A','D','U'))
BEGIN
DECLARE @DBID INT;
SET @DBID = DB_ID();
DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();
RAISERROR
(N'資料庫 ID:%d, 資料庫名稱: %s。 錯誤訊息：傳入的Action_Mode，必須是 A, U, D 其中一種',
10, -- Severity.
1, -- State.
@DBID, -- First substitution argument.
@DBNAME); -- Second substitution argument.
END
--宣告加密用變數
DECLARE
@encryp_uid NVARCHAR(max),
@encryp_pwd NVARCHAR(max),
@encryp_sort NVARCHAR(max)
--SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),@uid)
SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @uid))
--SET @encryp_pwd = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),@pwd)
SET @encryp_pwd = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @pwd))
SET @encryp_sort = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(NVARCHAR(10),@sort))
SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @uid))
--插入
IF (@action_type = 'A')
BEGIN
INSERT INTO t_BPM_Seal_type_E
(
username ,
[uid] ,
pwd ,
sort ,
cdt
)
VALUES
(
@username,
@encryp_uid,
@encryp_pwd,
@encryp_sort,
@cdt
)
END
--更新
IF (@action_type = 'U')
BEGIN
UPDATE t_BPM_Seal_type_E
SET username=@username,
[uid]=@encryp_uid,
pwd=@encryp_pwd,
cdt=@cdt
WHERE id=@id
END
--刪除
IF (@action_type = 'D')
BEGIN
DELETE t_BPM_Seal_type_E
WHERE id=@id
END
END
GO
5.建立檢視資料表，用來讀取被加密的資料表
CREATE VIEW [dbo].[v_BPM_Seal_type_E]
AS
SELECT id, username, CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), uid, N'p@ssw0rd')) AS uid, CONVERT(NVARCHAR(50),
DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), pwd, N'p@ssw0rd')) AS pwd, CONVERT(NVARCHAR(50),
DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), sort, N'p@ssw0rd')) AS sort, cdt
FROM dbo.t_BPM_Seal_type_E
6.最後查詢被加密的資料表。
上面是未解密原資料表，下面是已解密的檢視(使用第5步驟建立的檢視表)
7.直接查詢，不透過檢視表(View)
Select id,
ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),
CONVERT(nvarchar(max), username)) as '加密'
From dbo.t_BPM_Seal_type_E
Select id,
CONVERT(nvarchar(10), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), uid, N'p@ssw0rd')) as '解密'
From dbo.t_BPM_Seal_type_E
