Create procedure sp_decrypt (@ objectname varchar (50 ))
As
Begin
Begin tran
Declare @ objectname1 varchar (100), @ orgvarbin varbinary (8000)
Declare @ sql1 nvarchar (4000), @ sql2 nvarchar (4000), @ sql3 nvarchar (4000), @ sql4 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), @ resulnvtsp archar (4000)
Declare @ I int, @ status int, @ Type varchar (10), @ parentid int
Declare @ colid int, @ n int, @ Q int, @ J int, @ k int, @ encrypted int, @ number int
Select @ 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 # temp
Set @ k = 0
While @ k <= @ number
Begin
If exists (select 1 from syscomments where id = object_id (@ objectname) and number = @ k)
Begin
If @ type = 'P'
Set @ sql1 = (case when @ number> 1 then 'alter procedure '+ @ objectname +'; '+ rtrim (@ k) +' with encryption'
Else 'alter procedure '+ @ objectname +' with encryption'
End)
If @ type = 'tr'
Set @ sql1 = 'alter trigger' + @ objectname + 'on' + object_name (@ parentid) + 'with encryption for insert as print 1'
If @ type = 'fn 'or @ type = 'tf' or @ type = 'if'
Set @ sql1 = (Case @ type when 'tf' then
'Alter function' + @ objectname + '(@ a char (1) returns @ B Table (A varchar (10 )) with encryption as begin insert @ B select @ a return end'
When 'fn 'then
'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'
End)
If @ type = 'V'
Set @ sql1 = 'alter view' + @ objectname + 'with encryption as select 1'
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)
End
Set @ k = @ k + 1
End
Set @ k = 0
While @ k <= @ number
Begin
If exists (select 1 from syscomments where id = object_id (@ objectname) and number = @ k)
Begin
Select @ colid = max (colid) from # temp where number = @ k
Set @ n = 1
While @ n <= @ colid
Begin
Select @ 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 = 1
Begin
If @ type = 'P'
Set @ origsptext2 = (case when @ number> 1 then 'create procedure '+ @ objectname +'; '+ rtrim (@ k) +' with encryption'
Else 'create procedure '+ @ objectname +' with encryption'
End)
If @ type = 'fn 'or @ type = 'tf' or @ type = 'if' -- correct the error.
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 'then
'Create function' + @ objectname + '(@ a char (1) returns char (1) with encryption as begin return @ A end'
When 'if' then
'Create function' + @ objectname + '(@ a char (1) returns table with encryption as return select @ A as'
End)
If @ type = 'tr'
Set @ origsptext2 = 'create trigger' + @ objectname + 'on' + object_name (@ parentid) + 'with encryption for insert as print 1'
If @ type = 'V'
Set @ origsptext2 = 'create view' + @ objectname + 'with encryption as select 1'
Set @ q = 4000-len (@ origsptext2)
Set @ origsptext2 = @ origsptext2 + replicate ('-', @ q)
End
Else
Begin
Set @ origsptext2 = replicate ('-', 4000)
End
-- Start counter
Set @ I = 1
-- Fill temporary variable
Set @ resultsp = replicate (N 'A', (datalength (@ origsptext1)/2 ))
-- Loop
While @ I <= datalength (@ origsptext1)/2
Begin
Set @ resultsp = stuff (@ resultsp, @ I, 1, nchar (Unicode (substring (@ origsptext1, @ I, 1) ^
(Unicode (substring (@ origsptext2, @ I, 1) ^
Unicode (substring (@ origsptext3, @ I, 1 )))))
Set @ I = @ I + 1
End
Set @ orgvarbin = cast (@ origsptext1 as varbinary (8000 ))
Set @ resultsp = (case when @ encrypted = 1
Then @ resultsp
Else convert (nvarchar (4000), case when @ Status & 2 = 2 then uncompress (@ orgvarbin) else @ orgvarbin end)
End)
Print @ resultsp
-- Execute (@ resultsp)
Set @ n = @ n + 1
End
End
Set @ k = @ k + 1
End
Drop table # temp
Rollback tran
End
Go
Exec sp_decrypt 'P _ entrust'