--1、建立已加密的存储过程
USE AdventureWorks
GO
CREATE PROC test
    WITH ENCRYPTION
AS
    SELECT  SUSER_SNAME() ,
            USER_NAME()
GO
--2、将上述定义内容去除，利用短语加密搭配EncryptByPassPhrase函数加密，然后在用sys.sp_addextendedproperty存储过程，指定一个扩展名称。
USE AdventureWorks
GO
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO'

--3、将内容加密后转换成sql_variant数据类型
DECLARE @bsql SQL_VARIANT
SET @bsql = ( SELECT    CONVERT(SQL_VARIANT, ENCRYPTBYPASSPHRASE('P@ssw0rd',
                                                              CONVERT(VARCHAR(MAX), @sql)))
            )

--4、新增到指定存储过程的扩展属性中：
EXEC sys.sp_addextendedproperty @name = N'test定义', @value = N'System.Byte[]',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE',
    @level1name = N'test'
GO
EXEC sys.sp_addextendedproperty @name = N'代码内容',
    @value = N'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO',
    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE',
    @level1name = N'test'
GO

--5、还原
DECLARE @pwd VARCHAR(100)= 'P@ssw0rd'
--密码短语

DECLARE @proc VARCHAR(100)= 'test'
--存储过程名

DECLARE @exName NVARCHAR(100)= '代码内容'
--扩充属性名

--将原本结果查询
SELECT  value
FROM    sys.all_objects AS sp
        INNER JOIN sys.extended_properties AS P ON P.major_id = sp.object_id
                                                   AND P.minor_id = 0
                                                   AND P.class = 1
WHERE   ( P.name = @exName )
        AND ( ( sp.type = N'p'
                OR sp.type = N'rf'
                OR sp.type = 'pc'
              )
              AND ( sp.name = @proc
                    AND SCHEMA_NAME(sp.schema_id) = N'dbo'
                  )
            )