如何解密SQL2005视图和存储过程
如题
我有几个数据库在SQL2000下视图和存储过程可以解密
转移到SQL2005下原来的解密脚本就不适合了
求一个能在2005下解密视图和存储过程的脚本
[解决办法]
/*================================================================================== NAME: Decrypt SQL 2005 stored procedures, functions, views, and triggers DESCRIPTION: HEADS UP: In order to run this script you must log in to the server in DAC mode: To do so, type ADMIN:<SQLInstanceName> as your server name and use the "sa" or any other server admin user with the appropriate password. CAUTION! DAC (dedicated admin access) will kick out all other server users. The script below accepts an object (schema name + object name) that were created using the WITH ENCRYPTION option and returns the decrypted script that creates the object. This script is useful to decrypt stored procedures, views, functions, and triggers that were created WITH ENCRYPTION. The algorithm used below is the following: 1. Check that the object exists and that it is encrypted. 2. In order to decrypt the object, the script ALTER (!!!) it and later restores the object to its original one. This is required as part of the decryption process: The object is altered to contain dummy text (the ALTER uses WITH ENCRYPTION) and then compared to the CREATE statement of the same dummy content. Note: The object is altered in a transaction, which is rolled back immediately after the object is changed to restore all previous settings. 3. A XOR operation between the original binary stream of the enrypted object with the binary representation of the dummy object and the binary version of the object in clear-text is used to decrypt the original object.USER PARAMETERS: @ObjectOwnerOrSchema @ObjectName RESULTSET: NA RESULTSET SORT: NA USING TABLES/VIEWS: sys.sysobjvalues syscomments REVISIONS DATE DEVELOPER DESCRIPTION OF REVISION VERSION ========= =============== ================================= =========== 01/01/2007 Omri Bahat Initial release 1.00 ================================================================================== Copyright SQL Farms Solutions, www.sqlfarms.com. All rights reserved. This code can be used only for non-redistributable purposes. The code can be used for free as long as this copyright notice is not removed. ==================================================================================*/ DECLARE @ObjectOwnerOrSchema NVARCHAR(128) DECLARE @ObjectName NVARCHAR(128) SET @ObjectOwnerOrSchema = 'dbo' SET @ObjectName = 'myproc' DECLARE @i INT DECLARE @ObjectDataLength INT DECLARE @ContentOfEncryptedObject NVARCHAR(MAX) DECLARE @ContentOfDecryptedObject NVARCHAR(MAX) DECLARE @ContentOfFakeObject NVARCHAR(MAX) DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX) DECLARE @ObjectType NVARCHAR(128) DECLARE @ObjectID INT SET NOCOUNT ON SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']') -- Check that the provided object exists in the database. IF @ObjectID IS NULL BEGIN RAISERROR('The object name or schema provided does not exist in the database', 16, 1) RETURN END -- Check that the provided object is encrypted. IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1) BEGIN RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1) RETURN END -- Determine the type of the object IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL SET @ObjectType = 'PROCEDURE' ELSE IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL SET @ObjectType = 'TRIGGER' ELSE IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL SET @ObjectType = 'VIEW' ELSE SET @ObjectType = 'FUNCTION' -- Get the binary representation of the object- syscomments no longer holds -- the content of encrypted object. SELECT TOP 1 @ContentOfEncryptedObject = imageval FROM sys.sysobjvalues WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']') AND valclass = 1 and subobjid = 1 SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2 -- We need to alter the existing object and make it into a dummy object -- in order to decrypt its content. This is done in a transaction -- (which is later rolled back) to ensure that all changes have a minimal -- impact on the database. SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS' WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength BEGIN IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000) ELSE SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) END -- Since we need to alter the object in order to decrypt it, this is done -- in a transaction SET XACT_ABORT OFF BEGIN TRAN EXEC(@ContentOfFakeObject) IF @@ERROR <> 0 ROLLBACK TRAN -- Get the encrypted content of the new "fake" object. SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval FROM sys.sysobjvalues WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']') AND valclass = 1 and subobjid = 1 IF @@TRANCOUNT > 0 ROLLBACK TRAN -- Generate a CREATE script for the dummy object text. SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS' WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength BEGIN IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000) ELSE SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) END SET @i = 1 --Fill the variable that holds the decrypted data with a filler character SET @ContentOfDecryptedObject = N'' WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength BEGIN IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000) ELSE SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2)) END WHILE @i <= @ObjectDataLength BEGIN --xor real & fake & fake encrypted SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1, NCHAR( UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^ ( UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1)) ))) SET @i = @i + 1 END -- PRINT the content of the decrypted object PRINT(@ContentOfDecryptedObject)分享到：
[解决办法]
SQLServer2005里使用with encryption选项创建的存储过程仍然和sqlserver2000里一样，都是使用XOR进行了的加密。和2000不一样的是，在2005的系统表syscomments里已经查不到加密过的密文了。要查密文必须使用DAC（专用管理员连接）连接到数据库后，在系统表sys.sysobjvalues查询，该表的列imageval存储了相应的密文。具体可以使用下面的查询：SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) ANDvalclass = 1 AND subobjid = 1下面是解密的存储过程，具体代码如下(这是版本4.0，最新的，修正很长的存储过程解密出来是空白的问题)：Create PROCEDURE [dbo].[sp__windbi$decrypt](@procedure sysname = NULL, @revfl int = 1)AS/*王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com调用形式为：exec dbo.sp__windbi$decrypt @procedure,0如果第二个参数使用1的话，会给出该存储过程的一些提示。--版本4.0 修正存储过程过长解密出来是空白的问题*/SET NOCOUNT ONIF @revfl = 1BEGINPRINT '警告：该存储过程会删除并重建原始的存储过程。'PRINT ' 在运行该存储过程之前确保你的数据库有一个备份。'PRINT ' 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。'PRINT ' 为了运行这个存储过程，将参数@refl的值更改为0。'RETURN 0ENDDECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength intselect @maxColID = max(subobjid) FROMsys.sysobjvalues WHERE objid = object_id(@procedure)--select @maxColID as 'Rows in sys.sysobjvalues'select @procNameLength = datalength(@procedure) + 29DECLARE @real_01 nvarchar(max)DECLARE @fake_01 nvarchar(max)DECLARE @fake_encrypt_01 nvarchar(max)DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)declare @objtype varchar(2),@ParentName nvarchar(max)select @real_decrypt_01a = ''-- 提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称select @objtype=type,@parentname=object_name(parent_object_id)from sys.objects where [object_id]=object_id(@procedure)-- 从sys.sysobjvalues里提出加密的imageval记录SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =object_id(@procedure) and valclass = 1 order by subobjid)--创建一个临时表create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,[real_decrypt] NVARCHAR(MAX) )--开始一个事务，稍后回滚BEGIN TRAN--更改原始的存储过程，用短横线替换if @objtype='P' SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1 /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'else if @objtype='FN' SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'else if @objtype='V' SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'else if @objtype='TR' SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10) /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'EXECUTE (@fake_01)--从sys.sysobjvalues里提出加密的假的SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =object_id(@procedure) and valclass = 1 order by subobjid )if @objtype='P' SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS select 1 /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'else if @objtype='FN' SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END'else if @objtype='V' SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'else if @objtype='TR' SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10) /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/'--开始计数SET @intProcSpace=1--使用字符填充临时变量SET @real_decrypt_01 = replicate(cast('A'as nvarchar(max)), (datalength(@real_01) /2 ))--循环设置每一个变量，创建真正的变量--每次一个字节SET @intProcSpace=1--如有必要，遍历每个@real_xx变量并解密WHILE @intProcSpace<=(datalength(@real_01)/2)BEGIN--真的和假的和加密的假的进行异或处理SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))SET @intProcSpace=@intProcSpace+1END--通过sp_helptext逻辑向表#output里插入变量insert #output (real_decrypt) select @real_decrypt_01--select real_decrypt AS '#output chek' from #output --测试-- ---------------------------------------开始从sp_helptext提取-- -------------------------------------declare @dbname sysname,@BlankSpaceAdded int,@BasePos int,@CurrentPos int,@TextLength int,@LineId int,@AddOnLen int,@LFCR int --回车换行的长度,@DefinedLength int,@SyscomText nvarchar(max),@Line nvarchar(255)Select @DefinedLength = 255SELECT @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格CREATE TABLE #CommentText(LineId int,Text nvarchar(255) collate database_default)--使用#output代替sys.sysobjvaluesDECLARE ms_crs_syscom CURSOR LOCALFOR SELECT real_decrypt from #outputORDER BY identFOR READ ONLY--获取文本SELECT @LFCR = 2SELECT @LineId = 1OPEN ms_crs_syscomFETCH NEXT FROM ms_crs_syscom into @SyscomTextWHILE @@fetch_status >= 0BEGINSELECT @BasePos = 1SELECT @CurrentPos = 1SELECT @TextLength = LEN(@SyscomText)WHILE @CurrentPos != 0BEGIN--通过回车查找行的结束SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,@BasePos)--如果找到回车IF @CurrentPos != 0BEGIN--如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续While (isnull(LEN(@Line),0) + @BlankSpaceAdded +@CurrentPos-@BasePos + @LFCR) > @DefinedLengthBEGINSELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +@BlankSpaceAdded)INSERT #CommentText VALUES( @LineId,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,@BasePos, @AddOnLen), N''))SELECT @Line = NULL, @LineId = @LineId + 1,@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0ENDSELECT @Line = isnull(@Line, N'') +isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')SELECT @BasePos = @CurrentPos+2INSERT #CommentText VALUES( @LineId, @Line )SELECT @LineId = @LineId + 1SELECT @Line = NULLENDELSE--如果回车没找到BEGINIF @BasePos <= @TextLengthBEGIN--如果@Lines长度的新值大于定义的长度While (isnull(LEN(@Line),0) + @BlankSpaceAdded +@TextLength-@BasePos+1 ) > @DefinedLengthBEGINSELECT @AddOnLen = @DefinedLength -(isnull(LEN(@Line),0) + @BlankSpaceAdded)INSERT #CommentText VALUES( @LineId,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,@BasePos, @AddOnLen), N''))SELECT @Line = NULL, @LineId = @LineId + 1,@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =0ENDSELECT @Line = isnull(@Line, N'') +isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')if LEN(@Line) < @DefinedLength and charindex(' ',@SyscomText, @TextLength+1 ) > 0BEGINSELECT @Line = @Line + ' ', @BlankSpaceAdded = 1ENDENDENDENDFETCH NEXT FROM ms_crs_syscom into @SyscomTextENDIF @Line is NOT NULLINSERT #CommentText VALUES( @LineId, @Line )select Text from #CommentText order by LineIdCLOSE ms_crs_syscomDEALLOCATE ms_crs_syscomDROP TABLE #CommentText-- ---------------------------------------结束从 sp_helptext提取-- ---------------------------------------删除用短横线创建的存储过程并重建原始的存储过程ROLLBACK TRANDROP TABLE #outputgo