------用户注册的存储过程
create proc pro_useregister
@loginname as nvarchar(10),
@passwords as nvarchar(10)
as
declare @sqlStr  nvarchar(2000)
------创建对称密钥
set @sqlStr='create symmetric key '+ @loginname+' with algorithm=des,key_source='''+ @loginname+''',identity_value='''+ @loginname+'''encryption by password='''+@passwords+''''
exec sp_executesql @sqlStr

------插入用户数据
insert into user_info values(@loginname,'authorized')
------打开密钥
set @sqlStr='open symmetric key '+@loginname+' decryption by password='''+@passwords+''''
exec sp_executesql @sqlStr

------加密用户数据
set @sqlStr='update user_info set passwords=encryptbykey(key_guid( '''+@loginname+''' ),passwords) where username='''+@loginname+''''
exec sp_executesql @sqlStr

------关闭密钥
set @sqlStr= 'close symmetric key '+@loginname
exec sp_executesql @sqlStr

------用户登录验证的存储过程
create proc pro_logincheck
@loginname as nvarchar(10),
@passwords as nvarchar(10)
as
declare @sqlStr  nvarchar(2000)

------打开密钥
set @sqlStr='open symmetric key '+@loginname+' decryption by password='''+@passwords+''''
exec sp_executesql @sqlStr

------查询用户数据
select username,convert(nvarchar,decryptbykey(passwords)) as password from user_info where username=@loginname
------关闭密钥
set @sqlStr= 'close symmetric key '+@loginname
exec sp_executesql @sqlStr