Desencriptar SP SQL Server: DECRYPT SQL SERVER 2000 STORED PROCEDURES, VIEWS AND TRIGGERS (WITH EXAMPLES)

This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption" There are 2 versions: one for SP's only and the other one for SP's, triggers and views version 1: INPUT: object name (stored procedure, view or trigger) version 2: INPUT: object name (stored procedure, view or trigger), object type('T'-trigger, 'P'-stored procedure or 'V'-view)

This example was executed in the version Sql Server 2000.

Tables Used

1. Syscomments

2. Sysobjects

In the enterprise manager create new store procedure

Create new Store Procedure

In the query analyzer execute the following code for create the store procedure and execute the sp_helptext for view in console

create to

dbo.DECRYPTSP2K

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE DECRYPTSP2K (@objName varchar(50))
AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d nvarchar(4000), @i int, @t bigint
--get encrypted data
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
EXECUTE (@b)
--get encrypted bogus SP
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
--start counter
SET @i=1
--fill temporary variable
SET @d = replicate(N'A', (datalength(@a) / 2))
--loop
WHILE @i<=datalength(@a)/2
   BEGIN
--xor original+bogus+bogus encrypted
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
   SET @i=@i+1
   END
--drop original SP
EXECUTE ('drop PROCEDURE '+ @objName)
--remove encryption
--try to preserve case
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
   SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
--replace SP
execute( @d)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

dbo.DECRYPT2K

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE DECRYPT2K (@objName varchar(50), @type char(1) )
AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d nvarchar(4000), @i int, @t bigint, @tablename varchar(255), @trigtype varchar(6)
SET @type=UPPER(@type)
IF @type='T'
    BEGIN
    SET @tablename=(SELECT sysobjects_1.name
    FROM dbo.sysobjects INNER JOIN
    dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
    WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
    SET @trigtype=(SELECT CASE WHEN dbo.sysobjects.deltrig > 0 THEN 'DELETE'
                    WHEN dbo.sysobjects.instrig > 0 THEN 'INSERT'
                    WHEN dbo.sysobjects.updtrig > 0 THEN 'UPDATE' END
            FROM dbo.sysobjects INNER JOIN
            dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
            WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
    END
--get encrypted data
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b=case @type
        WHEN 'S' THEN 'ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
        WHEN 'V' THEN 'ALTER VIEW '+ @objName +' WITH ENCRYPTION AS SELECT dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', 4000-150)
        WHEN 'T' THEN 'ALTER TRIGGER '+@objName+' ON '+ @tablename+' WITH ENCRYPTION FOR '+@trigtype+' AS PRINT ''a'''+REPLICATE('-', 4000-150)
        END
EXECUTE (@b)
--get encrypted bogus SP
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b=case @type
    WHEN 'S' THEN 'CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
    WHEN 'V' THEN 'CREATE VIEW '+ @objName +' WITH ENCRYPTION AS SELECT dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', 4000-150)
    WHEN 'T' THEN 'CREATE TRIGGER '+@objName+' ON '+ @tablename+' WITH ENCRYPTION FOR '+@trigtype+' AS PRINT ''a'''+REPLICATE('-', 4000-150)
    END
--start counter
SET @i=1
--fill temporary variable
SET @d = replicate(N'A', (datalength(@a) / 2))
--loop
WHILE @i<=datalength(@a)/2
    BEGIN
--xor original+bogus+bogus encrypted
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
    SET @i=@i+1
    END
--drop original SP
IF @type='S'
    EXECUTE ('drop PROCEDURE '+ @objName)
ELSE
    IF @type='V'
        EXECUTE ('drop VIEW '+ @objName)
    ELSE
        IF @type='T'
            EXECUTE ('drop TRIGGER '+ @objName)
--remove encryption
--try to preserve case
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
    SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
--replace SP
execute( @d)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Running the code: exec sp_helptext hello