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里提出加密的imageval记录 46 SET @real_01 = ( SELECT TOP 1 47                         imageval 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                                 imageval 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 NVARCHAR(MAX)),100                       DATALENGTH(@real_01) / 2 - @procNameLength) + ‘*/ END‘101     ELSE 102         IF @objtype = ‘V‘ 103             SET @fake_01 = ‘Create view ‘ + @procedure + ‘ WITH ENCRYPTION AS select 1 as col104   /**//*‘ + REPLICATE(CAST(‘*‘ AS NVARCHAR(MAX)),105                       DATALENGTH(@real_01) / 2 - @procNameLength) + ‘*/‘106         ELSE 107             IF @objtype = ‘TR‘ 108                 SET @fake_01 = ‘Create trigger ‘ + @procedure + ‘ ON ‘ + @parentname + ‘WITH ENCRYPTION AFTER INSERT AS RAISERROR (‘‘N‘‘,16,10)109   /**//*‘ + REPLICATE(CAST(‘*‘ AS NVARCHAR(MAX)),110                       DATALENGTH(@real_01) / 2 - @procNameLength) + ‘*/‘111 --开始计数112 SET @intProcSpace = 1113 --使用字符填充临时变量114 SET @real_decrypt_01 = REPLICATE(CAST(‘A‘ AS NVARCHAR(MAX)),115                                  ( DATALENGTH(@real_01) / 2 ))116 --循环设置每一个变量，创建真正的变量117 --每次一个字节118 SET @intProcSpace = 1119 --如有必要，遍历每个@real_xx变量并解密120 WHILE @intProcSpace <= ( DATALENGTH(@real_01) / 2 ) 121     BEGIN122 --真的和假的和加密的假的进行异或处理123         SET @real_decrypt_01 = STUFF(@real_decrypt_01, @intProcSpace, 1,124                                      NCHAR(UNICODE(SUBSTRING(@real_01,125                                                              @intProcSpace, 1)) ^ ( UNICODE(SUBSTRING(@fake_01,126                                                               @intProcSpace, 1)) ^ UNICODE(SUBSTRING(@fake_encrypt_01,127                                                               @intProcSpace, 1)) )))128         SET @intProcSpace = @intProcSpace + 1129     END130 --通过sp_helptext逻辑向表#output里插入变量131 INSERT  #output ( real_decrypt )132         SELECT  @real_decrypt_01133 --select real_decrypt AS ‘#output chek‘ from #output --测试134 -- -------------------------------------135 --开始从sp_helptext提取136 -- -------------------------------------137 DECLARE @dbname SYSNAME ,138     @BlankSpaceAdded INT ,139     @BasePos INT ,140     @CurrentPos INT ,141     @TextLength INT ,142     @LineId INT ,143     @AddOnLen INT ,144     @LFCR INT --回车换行的长度145     ,146     @DefinedLength INT ,147     @SyscomText NVARCHAR(MAX) ,148     @Line NVARCHAR(255)149 SELECT  @DefinedLength = 255150 SELECT  @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格151 CREATE TABLE #CommentText152 (153   LineId INT ,154   Text NVARCHAR(255) COLLATE database_default155 )156 --使用#output代替sys.sysobjvalues157 DECLARE ms_crs_syscom CURSOR LOCAL158 FOR159 SELECT  real_decrypt160 FROM    #output161 ORDER BY ident FOR READ ONLY162 --获取文本163 SELECT  @LFCR = 2164 SELECT  @LineId = 1165 OPEN ms_crs_syscom166 FETCH NEXT FROM ms_crs_syscom INTO @SyscomText167 168 WHILE @@fetch_status >= 0 169     BEGIN170         SELECT  @BasePos = 1171         SELECT  @CurrentPos = 1172         SELECT  @TextLength = LEN(@SyscomText)173         WHILE @CurrentPos != 0 174             BEGIN175         --通过回车查找行的结束176                 SELECT  @CurrentPos = CHARINDEX(CHAR(13) + CHAR(10),177                                                 @SyscomText, @BasePos)178         --如果找到回车179                 IF @CurrentPos != 0 180                     BEGIN181                 --如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续182                         WHILE ( ISNULL(LEN(@Line), 0) + @BlankSpaceAdded + @CurrentPos - @BasePos + @LFCR ) > @DefinedLength 183                             BEGIN184                                 SELECT  @AddOnLen = @DefinedLength - ( ISNULL(LEN(@Line),185                                                               0) + @BlankSpaceAdded )186                                 INSERT  #CommentText187                                 VALUES  ( @LineId,188                                           ISNULL(@Line, N‘‘) + ISNULL(SUBSTRING(@SyscomText,189                                                               @BasePos,190                                                               @AddOnLen), N‘‘) )191                                 SELECT  @Line = NULL, @LineId = @LineId + 1,192                                         @BasePos = @BasePos + @AddOnLen,193                                         @BlankSpaceAdded = 0194                             END195                         SELECT  @Line = ISNULL(@Line, N‘‘) + ISNULL(SUBSTRING(@SyscomText,196                                                               @BasePos,197                                                               @CurrentPos - @BasePos + @LFCR),198                                                               N‘‘)199                         SELECT  @BasePos = @CurrentPos + 2200                         INSERT  #CommentText201                         VALUES  ( @LineId, @Line )202                         SELECT  @LineId = @LineId + 1203                         SELECT  @Line = NULL204                     END205                 ELSE206 --如果回车没找到207                     BEGIN208                         IF @BasePos <= @TextLength 209                             BEGIN210                         --如果@Lines长度的新值大于定义的长度211                                 WHILE ( ISNULL(LEN(@Line), 0) + @BlankSpaceAdded + @TextLength - @BasePos + 1 ) > @DefinedLength 212                                     BEGIN213                                         SELECT  @AddOnLen = @DefinedLength - ( ISNULL(LEN(@Line),214                                                               0) + @BlankSpaceAdded )215                                         INSERT  #CommentText216                                         VALUES  ( @LineId,217                                                   ISNULL(@Line, N‘‘) + ISNULL(SUBSTRING(@SyscomText,218                                                               @BasePos,219                                                               @AddOnLen), N‘‘) )220                                         SELECT  @Line = NULL,221                                                 @LineId = @LineId + 1,222                                                 @BasePos = @BasePos + @AddOnLen,223                                                 @BlankSpaceAdded = 0224                                     END225                                 SELECT  @Line = ISNULL(@Line, N‘‘) + ISNULL(SUBSTRING(@SyscomText,226                                                               @BasePos,227                                                               @TextLength - @BasePos + 1),228                                                               N‘‘)229                                 IF LEN(@Line) < @DefinedLength AND CHARINDEX(‘ ‘,230                                                               @SyscomText,231                                                               @TextLength + 1) > 0 232                                     BEGIN233                                         SELECT  @Line = @Line + ‘ ‘,234                                                 @BlankSpaceAdded = 1235                                     END236                             END237                     END238             END239         FETCH NEXT FROM ms_crs_syscom INTO @SyscomText240     END241     242     243 IF @Line IS NOT NULL 244     INSERT  #CommentText245     VALUES  ( @LineId, @Line )246 SELECT  Text247 FROM    #CommentText248 ORDER BY LineId249 CLOSE ms_crs_syscom250 DEALLOCATE ms_crs_syscom251 DROP TABLE #CommentText252 -- -------------------------------------253 --结束从sp_helptext提取254 -- -------------------------------------255 --删除用短横线创建的存储过程并重建原始的存储过程256 ROLLBACK TRAN257 DROP TABLE #output258 GO

1 USE PRATICE2 GO3 EXEC master.dbo.sp__windbi$decrypt ‘dbo.creat_test_Encryption‘, 0

1 消息 208，级别 16，状态 1，过程 sp__windbi$decrypt，第 26 行2 对象名 ‘sys.sysobjvalues‘ 无效。