SQL2005解密已经被加密的存储过程(一)

1 USE master
2 GO
3 CREATE PROCEDURE [dbo].[sp__windbi$decrypt]
4 (
5 @procedure SYSNAME = NULL ,
6 @revfl INT = 1
7 )
8 AS /**//*
9 王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
10 调用形式为：
11 exec dbo.sp__windbi$decrypt @procedure,0
12 如果第二个参数使用1的话，会给出该存储过程的一些提示。
13 --版本4.0 修正存储过程过长解密出来是空白的问题
14 */
15 SET NOCOUNT ON
16 IF @revfl = 1
17 BEGIN
18 PRINT '警告：该存储过程会删除并重建原始的存储过程。'
19 PRINT ' 在运行该存储过程之前确保你的数据库有一个备份。'
20 PRINT ' 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'
21 PRINT ' 为了运行这个存储过程，将参数@refl的值更改为0。'
22 RETURN 0
23 END
24 DECLARE @intProcSpace BIGINT ,
25 @t BIGINT ,
26 @maxColID SMALLINT ,
27 @procNameLength INT
28 SELECT @maxColID = MAX(subobjid)
29 FROM sys.sysobjvalues
30 WHERE objid = OBJECT_ID(@procedure)
31 --select @maxColID as 'Rows in sys.sysobjvalues'
32 SELECT @procNameLength = DATALENGTH(@procedure) + 29
33 DECLARE @real_01 NVARCHAR(MAX)
34 DECLARE @fake_01 NVARCHAR(MAX)
35 DECLARE @fake_encrypt_01 NVARCHAR(MAX)
36 DECLARE @real_decrypt_01 NVARCHAR(MAX) ,
37 @real_decrypt_01a NVARCHAR(MAX)
38 DECLARE @objtype VARCHAR(2) ,
39 @ParentName NVARCHAR(MAX)
40 SELECT @real_decrypt_01a = ''
41 --提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称
42 SELECT @objtype = type, @parentname = OBJECT_NAME(parent_object_id)
43 FROM sys.objects
44 WHERE [object_id] = OBJECT_ID(@procedure)
45 -- 从sys.sysobjvalues里提出 加密的imageva l记录
46 SET @real_01 = ( SELECT TOP 1
47 imageva l
48 FROM sys.sysobjvalues
49 WHERE objid = OBJECT_ID(@procedure) AND valclass = 1
50 ORDER BY subobjid
51 )
52 --创建一个临时表
53 CREATE TABLE #output
54 (
55 [ident] [int] IDENTITY(1, 1)
56 NOT NULL ,
57 [real_decrypt] NVARCHAR(MAX)
58 )
59 ------------------------------------------------------------
60
61 --开始一个事务，稍后回滚
62 BEGIN TRAN
63
64 --更改原始的存储过程，用短横线替换
65 IF @objtype = 'P'
66 SET @fake_01 = 'ALTER PROCEDURE ' + @procedure + ' WITH ENCRYPTION AS select 1
67 /**//*' + REPLICATE(CAST('*' AS NVARCHAR(MAX)),
68 DATALENGTH(@real_01) / 2 - @procNameLength) + '*/'
69 ELSE
70 IF @objtype = 'FN'
71 SET @fake_01 = 'ALTER FUNCTION ' + @procedure + '() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
72 /**//*' + REPLICATE(CAST('*' AS NVARCHAR(MAX)),
73 DATALENGTH(@real_01) / 2 - @procNameLength) + '*/ END'
74 ELSE
75 IF @objtype = 'V'
76 SET @fake_01 = 'ALTER view ' + @procedure + ' WITH ENCRYPTION AS select 1 as col
77 /**//*' + REPLICATE(CAST('*' AS NVARCHAR(MAX)),
78 DATALENGTH(@real_01) / 2 - @procNameLength) + '*/'
79 ELSE
80 IF @objtype = 'TR'
81 SET @fake_01 = 'ALTER trigger ' + @procedure + ' ON ' + @parentname + 'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10)
82 /**//*' + REPLICATE(CAST('*' AS NVARCHAR(MAX)),
83 DATALENGTH(@real_01) / 2 - @procNameLength) + '*/'
84 EXECUTE (@fake_01)
85 --从sys.sysobjvalues里提出加密的假的
86 SET @fake_encrypt_01 = ( SELECT TOP 1
87 imageva l
88 FROM sys.sysobjvalues
89 WHERE objid = OBJECT_ID(@procedure) AND valclass = 1
90 ORDER BY subobjid
91 )
92 IF @objtype = 'P'
93 SET @fake_01 = 'Create PROCEDURE ' + @procedure + ' WITH ENCRYPTION AS select 1
94 /**//*' + REPLICATE(CAST('*' AS NVARCHAR(MAX)),
95 DATALENGTH(@real_01) / 2 - @procNameLength) + '*/'
96 ELSE
97 IF @objtype = 'FN'
98 SET @fake_01 = 'CREATE FUNCTION ' + @procedure + '() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
99 /**//*' + REPLICATE(CAST('*' AS NVARCH