create PROCEDURE sp_decrypt (@objectName varchar (50)) ASbegin - j9988 copyright: 2004.01.05begin trandeclare @ objectname1 varchar (100), @ orgvarbin varbinary (8000) declare @ sql1 nvarchar (4000), @ sql2 nvarchar (4000), @ SQL3 nvarchar (4000), @ sql5 nvarchar (4000), @ sql6 nvarchar (4000), @ sql7 nvarchar (4000), @ SQL8 nVARCHAR (4000), @ SQL9 nVARCHAR (4000), @ SQL10 nvarchar (4000) DECLARE @ OrigSpText1 nvarchar (4000), @ OrigSpText2 nvarchar (4000), @ OrigSpText3 nvarchar (4000), @resultsp nvarchar (4000) declare @i int, @ status int, @ type varchar (10), @ parentid intdeclare @colid int, @ n int, @ q int, @ j int, @ k int, @ encrypted int, @ number intselect @ type = xtype, @ parentid = parent_obj from sysobjects where id = object_id (@ObjectName)
create table #temp (number int, colid int, ctext varbinary (8000), encrypted int, status int) insert #temp SELECT number, colid, ctext, encrypted, status FROM syscomments WHERE id = object_id (@objectName) select @ number = Max (Number) from #Tempset @ K = 0
while @k <= @ number beginif exists (select 1 from syscomments where id = object_id (@objectname) and number = @ k) beginif @ type = 'P'set @ sql1 = (case when @number> 1 then' ALTER PROCEDURE ' @Objectname '; ' RTRIM (@K) ' with encryption as' else 'alter procedure' @ Objectname 'with encryption as' end
IF @ type = 'tr'set @ SQL1 =' ALTER TRIGGER ' @ Objectname ' on ' Object_name (@parentid) ' with Encryption for Insert Asprint 1 'IF @ Type =' fn 'or @ type =' tf 'or @ type =' iF'set @ SQL1 = (Case @Type when 'TF' TEN 'ALTER FUNCTION' @Objectname '(@ a char (1)) Returns @B Table (a varchar (10)) with encryption As begin insert @B SELECT @a Return End 'When' Fn 'Ten'Alter Function' @Objectname '(@ a char (1)) Returns Char (1) with encryption as begin Return @a end'when' if ' Then'alter Function ' @Objectname ' (@ a char (1)) Returns Table with Encryption As Return SELECT @a as a'end)
IF @ type = 'V'SET @ SQL1 =' Alter View ' @ Objectname ' with Encryption As SELECT 1 AS F '
Set @ q = len (@ SQL1) set @ SQL1 = @ SQL1 Replicate ('-', 4000- @ q) SELECT @ SQL2 = Replicate ('-', 4000), @ SQL3 = Replicate ('-', 4000 ), @ SQL4 = Replicate ('-', 4000), @ SQL5 = Replicate ('-', 4000), @ SQL6 = Replicate ('-', 4000), @ SQL7 = Replicate ('-', 4000), @ SQL8 = Replicate ('-', 4000), @ SQL9 = Replicate ('-', 4000), @ SQL10 = Replicate ('-', 4000) EXEC (@ SQL1 @ SQL2 @ SQL3 @ SQL4 @ SQL5 @ SQL6 @ SQL7 @ SQL8 @ SQL9 @ SQL10) endset @ k = @ K 1nd
Set @ K = 0WHILE @K <= @ Number Begin
if exists (select 1 from syscomments where id = object_id (@objectname) and number = @ k) beginselect @ colid = max (colid) from #temp where number = set n = 1while @n @ @ k <= @ colidbeginselect @ OrigSpText1 = ctext, @ encrypted = encrypted, @ status = status from #temp where colid = @ n And Number = @ K
SET @ OrigSpText3 = (SELECT ctext FROM syscomments WHERE id = object_id (@objectName) and colid = @ n and number = @ k) if @ n = 1beginif @ type = 'P'SET @ OrigSpText2 = (case when @number> 1 THEN 'CREATE Procedure' @objectname ';' RTRIM (@K) 'with Encryption As' else 'Create Procedure' @ObjectName 'with encryption as' end)
IF @ type = 'fn' or @ type = 'f' - I have called set @ OrigspText2 = (Case @Type when 'Tf' Then 'Create Function' @Objectname '( @a char (1)) Returns @B Table (a varchar (10)) with encryption as begin insert @B select @a return end 'When' fn '1'create function ' @Objectname ' (@ a char (1 )) Returns char (1) with encryption as begin Return @a end'when 'f' Then'create Function ' @objectname ' (@ a char (1)) Returns Table with Encryption As Return SELECT @a as a'end )
IF @ type = 'tr'set @ origsptext2 =' create trigger ' @ Objectname ' on ' Object_name (@parentid) ' with encryption for Insert Asprint 1 '
if @ type = 'V'set @ OrigSpText2 =' CREATE VIEW ' @ objectname ' WITH ENCRYPTION AS SELECT 1 as f'set @ q = 4000-len (@ OrigSpText2) set @ OrigSpText2 = @ OrigSpText2 REPLICATE ( '-' , @ q) Endelsebeginset @ origsptext2 = replicate ('-', 4000) end - start counterset @ i = 1 - Fill Temporary variableSet @Resultsp = Replicate (N'A ', (Datalength (@ OrigspText1) / 2)))
--LoopWhile @i <= datalength (@ OrigspText1) / 2begin
Set @Resultsp = stuff (@Resultsp, @i, 1, nchar (unicode (@ ptext1, @i, 1)) ^ (unicode (@ ptext2, @i, 1)) ^ unicode (@ @ 1 OrigspText3, @i, 1)))))) SET @ i = @ i 1ndset @ orgvarbin = CAST (@ ptext1 as varbinary (8000)) set @ resultsp = (casten @ Encrypted = 1 Then @Resultsp Else Convert (nvarchar (4000), Case When @ status & 2 = 2 THEN UNCOMPRESS (@orgvarbin) Else @orgvarbin end) @Resultsp - Execute (@Resultsp) set @ n = @ n 1
end
Endset @ k = @ K 1END
Drop Table #TemProllback Tranend
Go
