Decrypting SQL SERVER 2005 cryptographic stored procedures, functions

You must use a dedicated administrative connection in SQL SERVER 2005 to view the tables that are used during the process

Eg:sqlcmd-a

1>use Test

2>go

1>sp_decrypt ' P_testa '

2>go

Text
----------------------
Create procedure P_testa

With encryption

As

SELECT * FROM Test

Create PROCEDURE [dbo]. [Sp_decrypt]
(@procedure sysname = NULL)
As
SET NOCOUNT on
BEGIN
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint, @intEncrypted
tinyint, @procNameLength int
Select @maxColID = max (Subobjid), @intEncrypted = Imageval from
Sys.sysobjvalues WHERE ObjID = object_id (@procedure)
GROUP by Imageval

Select @procNameLength = datalength (@procedure) + 29
DECLARE @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 = "
--Extract the type of the object as a stored procedure or function, and, if it is a trigger, get the name of its parent object
Select @objtype =type, @parentname =object_name (parent_object_id)
From sys.objects where [object_id]=object_id (@procedure)
--a imageval record of encryption from Sys.sysobjvalues
SET @real_01 = (SELECT top 1 imageval from sys.sysobjvalues WHERE ObjID =
OBJECT_ID (@procedure) and Valclass = 1 ORDER by Subobjid)
--Create a temporary table
CREATE Table #output ([ident] [int] IDENTITY (1, 1) not NULL,
[Real_decrypt] NVARCHAR (MAX))
--Start a transaction and roll back later
BEGIN TRAN
--Change the original stored procedure and replace it with a dash
If @objtype = ' P '
SET @fake_01 = ' ALTER PROCEDURE ' + @procedure + ' with encryption as
' +replicate ('-', 40003-@procNameLength)
else if @objtype = ' FN '
SET @fake_01 = ' ALTER FUNCTION ' + @procedure + ' () RETURNS INT with encryption as BEGIN RETURN 1
/* ' +replicate (' * ', datalength (@real_01)/2-@procNameLength) + ' */END '
else if @objtype = ' V '
SET @fake_01 = ' ALTER view ' + @procedure + ' with encryption as select 1 as Col
/* ' +replicate (' * ', datalength (@real_01)/2-@procNameLength) + ' * * '
else if @objtype = ' TR '
SET @fake_01 = ' ALTER trigger ' + @procedure + ' on ' [e-mail protected]+ ' with encryption after INSERT as RAISERROR (' N ', 16,10 )
/* ' +replicate (' * ', datalength (@real_01)/2-@procNameLength) + ' * * '
EXECUTE (@fake_01)
--From the sys.sysobjvalues of the encrypted fake
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
' +replicate ('-', 40003-@procNameLength)
else if @objtype = ' FN '
SET @fake_01 = ' CREATE FUNCTION ' + @procedure + ' () RETURNS INT with encryption as BEGIN RETURN 1
/* ' +replicate (' * ', datalength (@real_01)/2-@procNameLength) + ' */END '
else if @objtype = ' V '
SET @fake_01 = ' Create view ' + @procedure + ' with encryption as select 1 as Col
/* ' +replicate (' * ', datalength (@real_01)/2-@procNameLength) + ' * * '
else if @objtype = ' TR '
SET @fake_01 = ' Create trigger ' + @procedure + ' on ' [e-mail protected]+ ' with encryption after INSERT as RAISERROR (' N ', 16,1 0)
/* ' +replicate (' * ', datalength (@real_01)/2-@procNameLength) + ' * * '
--Start counting
SET @intProcSpace =1
--populating temporary variables with characters
SET @real_decrypt_01 = replicate (N ' A ', (datalength (@real_01)/2))
--cycle through each variable to create a real variable
--one byte at a time
SET @intProcSpace =1
-If necessary, iterate through each @real_xx variable and decrypt
While @intProcSpace <= (datalength (@real_01)/2)
BEGIN
--True and false and encrypted fake or processed
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 @[email protected]+1
END
--Inserting variables into the table #output by sp_helptext logic
Insert #output (real_decrypt) Select @real_decrypt_01
--Select Real_decrypt as ' #output Chek ' from #output--Test
-- -------------------------------------
--Start extracting from sp_helptext
-- -------------------------------------
declare @dbname sysname
, @BlankSpaceAdded int
, @BasePos int
, @CurrentPos int
, @TextLength int
, @LineId int
, @AddOnLen int
, @LFCR INT--The length of the carriage return line
, @DefinedLength int
, @SyscomText nvarchar (4000)
, @Line nvarchar (255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0-The space at the end of the trace line. Note that the Len function ignores the extra spaces
CREATE TABLE #CommentText
(LineId int
, Text nvarchar (255) collate Database_default)
--use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
For SELECT Real_decrypt from #output
ORDER by ident
For READ only
--Get text
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT from ms_crs_syscom to @SyscomText
While @ @fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN (@SyscomText)
While @CurrentPos! = 0
BEGIN
--Find the end of the line by carriage return
SELECT @CurrentPos = CHARINDEX (+char), @SyscomText,
@BasePos)
--If you find a carriage return
IF @CurrentPos! = 0
BEGIN
--If the new value of the length of the @lines is larger than the setting, insert @lines current content and continue
while (IsNull (LEN (@Line), 0) + @BlankSpaceAdded +
@[email protected] + @LFCR) > @DefinedLength
BEGIN
SELECT @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 = 0
END
SELECT @Line = IsNull (@Line, N ") +
IsNull (SUBSTRING (@SyscomText, @BasePos, @[email protected] + @LFCR), N ")
SELECT @BasePos = @CurrentPos +2
INSERT #CommentText VALUES (@LineId, @Line)
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--If the carriage return is not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
--If the new value of the @lines length is greater than the defined length
while (IsNull (LEN (@Line), 0) + @BlankSpaceAdded +
@[email protected]+1) > @DefinedLength
BEGIN
SELECT @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 =
0
END
SELECT @Line = IsNull (@Line, N ") +
IsNull (SUBSTRING (@SyscomText, @BasePos, @[email protected]+1), N ")
If LEN (@Line) < @DefinedLength and Charindex (",
@SyscomText, @TextLength +1) > 0
BEGIN
SELECT @Line = @Line + ", @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT from ms_crs_syscom to @SyscomText
END
IF @Line is not NULL
INSERT #CommentText VALUES (@LineId, @Line)
Select Text from #CommentText ORDER by LineId
CLOSE ms_crs_syscom
Deallocate ms_crs_syscom
DROP TABLE #CommentText
-- -------------------------------------
--End extraction from sp_helptext
-- -------------------------------------
--delete the stored procedure created with the dash and rebuild the original stored procedure
ROLLBACK TRAN
DROP TABLE #output

END

Decrypting SQL SERVER 2005 cryptographic stored procedures, functions