Decrypt the encrypted store procedure through the T-SQL programming in SQL Server 2005

Question

HI ,

 

I have a encrypted store procedure in my production server .Right now I do not have the script now I want do some modification.

Please any one can help me to decrypt the store procedure it will be a great help for me.

 

For any suggestion thanks  in advance.

---

" Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
Click here to read my blog

Answer 1

It is sad that  you do not have script all user stored procedures just before ensryption

The below script I found on internet is for SS2000 , see if that helps you anyway

 

 

create procedure hello
with encryption
as
print 'hello world'

sp_helptext hello
exec DECRYPTSP2K 'hello'

 

 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE DECRYPTSP2K (@objName varchar(50))
--INPUT: object name (stored procedure,
--    
-- view or trigger)
--Original idea: shoeboy <shoeboy@a
-- dequacy.org>
--Copyright © 1999-2002 SecurityFocus
--adapted by Joseph Gama
--Planet Source Code, my employer and my
--    
-- self are not responsible for the use
--     of
-- this code
--This code is provided as is and for ed
--    
-- ucational purposes only
--Please test it and share your results
 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

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

There are alot of tools to decrypt the sql server SPs.

Check here;

http://www.mssqltips.com/tip.asp?tip=1046

---

Please visit my Blog for some easy and often used t-sql scripts

Answer 3

The latest version of this code to decrypt sql objects can be found at www.jongurgul.com/blog/sql-object-decryption/

Here is some code that I have adapted based on some other articles on SQL2000 decryption.

***Put this stored procedure in your user database, and delete after use***

CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
	BEGIN

	DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
	SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
	SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

		BEGIN TRANSACTION
			EXECUTE (@b)
			SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)	
		ROLLBACK TRANSACTION

	SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
	SET @i=1
	WHILE @i<=(DATALENGTH(@a)/2)
	BEGIN
	SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
	SET @i=@i+1
	END

	SELECT @d [StoredProcedure]

	END
	ELSE
	BEGIN
		PRINT 'Use a DAC Connection'
	END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

Jon

Answer 4

Thanks to every one for the suggestion but i am looking the decript script in sql server 2005 still some where i am getting error when i am using the 2000 version script.

 

---

" Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
Click here to read my blog

Answer 5

what error are you getting?

---

Jon

Answer 6

The latest version of this code to decrypt sql objects can be found at www.jongurgul.com/blog/sql-object-decryption/
--***While I have tested this please be aware that bugs probably exist, and you use it entirely at your own risk.***--
--***Run this script using a DAC. Replace @objschemaname,@objname variable with your object, and check messages tab for decrypted***--

--Initial adaptation based on code by shoeboy/joseph gama 27/09/2010
--http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
--Updated should work for larger object text and a few other object types 16/02/2011
--http://cassianosanches.com/post/Como-Descriptografar-Procedures-e-Outros-Objetos-no-SQL-2005-e-2008.aspx
--Added Schema Support 06/03/2011

DECLARE @objid INT,@objname NVARCHAR(50),@objschemaname NVARCHAR(50),@objtype NVARCHAR(50),@objtypicalstm NVARCHAR(4000),@objencrypted BIT
SET @objschemaname = 'dbo'
SET @objname = 'xxx'

SELECT TOP 1 @objid=o,@objname = n,@objtype = t,@objtypicalstm=s,@objencrypted = (SELECT ([encrypted]) FROM syscomments WHERE [id] = x.o and colid = 1) 
FROM
(
SELECT object_id o, name n,
CASE WHEN [type] = 'P' THEN N'PROCEDURE' 
WHEN [type] = 'V' THEN 'VIEW' 
WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION' 
ELSE [type]
END t,
CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS' 
WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC' 
ELSE [type]
END s
FROM sys.all_objects WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF') 
AND name = @objname AND (SCHEMA_NAME([schema_id]) = COALESCE(@objschemaname,'dbo'))
--UNION ALL SELECT object_id,name,'TRIGGER',N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1' FROM sys.server_triggers WHERE name = @objname 
--UNION ALL SELECT object_id,name,'TRIGGER',N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1' FROM sys.triggers WHERE name = @objname
) x

SELECT @objid,@objname,@objtype,@objtypicalstm,@objencrypted

SET NOCOUNT ON

IF @objencrypted <> 0
BEGIN
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
	BEGIN
		DECLARE @ChunkNumber INT,@ChunkPiece NVARCHAR(MAX),@CompareChunksAtPosition INT,@DummyChunk NVARCHAR(MAX),@DummyObject VARBINARY(MAX),@EncryptedChunk NVARCHAR(MAX),@EncryptedObject VARBINARY(MAX),@p INT,@p1 NVARCHAR(MAX),@p2 NVARCHAR(MAX),@QueryForDummyObject NVARCHAR(MAX),@ReplacementText NVARCHAR(4000)

		SELECT @EncryptedObject = [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = @objid AND [valclass] = 1
			BEGIN TRANSACTION
			SET @p = 1
			SET @p1= N'ALTER'+SPACE(1)+@objtype+SPACE(1)+ISNULL((@objschemaname+'.'),'')+@objname +SPACE(1)+@objtypicalstm;	
			SET @p1=@p1+REPLICATE('-',4000-LEN(@p1))			
			SET @p2 = REPLICATE('-',8000)
			SET @QueryForDummyObject = N'EXEC(@p1'
				WHILE @p <=CEILING(DATALENGTH(@EncryptedObject) / 8000.0)
				BEGIN
				SET @QueryForDummyObject=@QueryForDummyObject+N'+@f'
				SET @p =@p +1
				END
			SET @QueryForDummyObject=@QueryForDummyObject+')'				
			EXEC sp_executesql @QueryForDummyObject,N'@p1 NVARCHAR(4000),@f VARCHAR(8000)',@p1=@p1,@f=@p2
			SET @DummyObject=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = @objid and [valclass] = 1)			
			ROLLBACK TRANSACTION
			SET @ChunkNumber=1
			WHILE @ChunkNumber<=CEILING(DATALENGTH(@EncryptedObject) / 8000.0)
			BEGIN		
			SELECT @EncryptedChunk = SUBSTRING(@EncryptedObject, (@ChunkNumber - 1) * 8000 + 1, 8000)
			SELECT @DummyChunk = SUBSTRING(@DummyObject, (@ChunkNumber - 1) * 8000 + 1, 8000)
				IF @ChunkNumber=1
				BEGIN		
				SET @ReplacementText=N'CREATE'+SPACE(1)+@objtype+SPACE(1)+ISNULL((@objschemaname+'.'),'')+@objname +SPACE(1)+@objtypicalstm+REPLICATE('-',4000)			
				END
				ELSE
				BEGIN
				SET @ReplacementText=REPLICATE('-', 4000)
				END				
			SET @ChunkPiece = REPLICATE(N'A', (DATALENGTH(@EncryptedChunk) / 2))					
			SET @CompareChunksAtPosition=1
			WHILE @CompareChunksAtPosition<=DATALENGTH(@EncryptedChunk)/2
				BEGIN
				SET @ChunkPiece = STUFF(@ChunkPiece, @CompareChunksAtPosition, 1, NCHAR(UNICODE(SUBSTRING(@EncryptedChunk, @CompareChunksAtPosition, 1)) ^	(UNICODE(SUBSTRING(@ReplacementText, @CompareChunksAtPosition, 1)) ^ UNICODE(SUBSTRING(@DummyChunk, @CompareChunksAtPosition, 1)))))
				SET @CompareChunksAtPosition=@CompareChunksAtPosition+1
				END

			PRINT @ChunkPiece
			SET @ChunkNumber=@ChunkNumber+1
			END		
	END
	ELSE
	BEGIN
		PRINT 'Use a DAC Connection'
	END
END
ELSE
BEGIN
	PRINT 'Object not encrypted or not found'
END
SET QUOTED_IDENTIFIER OFF

GO

Answer 7

I have modified Jon's post so it also supports functions correctly, jon's version gave a syntax error on functions because the dummy func sql was invalid

 

1. Connect using "admin:server\instance"  instead of "server\instance" (enable dac if you havent already, its an sp_reconfigure command)

2. create the below proc

3. exec SqlDecryptor 'dbo', 'function_or_stored_proc_name'

 

 

 

ALTER	-- CREATE
proc SqlDecryptor (@objschemaname nvarchar(255), @objname nvarchar(255))
AS

DECLARE @objid INT,@objtype NVARCHAR(50),@objtypicalstm NVARCHAR(4000),@objencrypted BIT

SELECT TOP 1 @objid=o,@objname = n,@objtype = t,@objtypicalstm=s,@objencrypted = (SELECT ([encrypted]) FROM syscomments WHERE [id] = x.o and colid = 1) 
FROM
(
SELECT object_id o, name n,
CASE WHEN [type] = 'P' THEN N'PROCEDURE' 
WHEN [type] = 'V' THEN 'VIEW' 
WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION' 
ELSE [type]
END t,
CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS' 
WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC' 
WHEN [type] IN ('FN','TF','IF') THEN N' () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' 
ELSE [type]
END s
FROM sys.all_objects WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF') 
AND name = @objname AND (SCHEMA_NAME([schema_id]) = COALESCE(@objschemaname,'dbo'))
--UNION ALL SELECT object_id,name,'TRIGGER',N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1' FROM sys.server_triggers WHERE name = @objname 
--UNION ALL SELECT object_id,name,'TRIGGER',N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1' FROM sys.triggers WHERE name = @objname
) x

--SELECT @objid,@objname,@objtype,@objtypicalstm,@objencrypted

SET NOCOUNT ON

IF @objencrypted <> 0
BEGIN
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
	BEGIN
		DECLARE @ChunkNumber INT,@ChunkPiece NVARCHAR(MAX),@CompareChunksAtPosition INT,@DummyChunk NVARCHAR(MAX),@DummyObject VARBINARY(MAX),@EncryptedChunk NVARCHAR(MAX),@EncryptedObject VARBINARY(MAX),@p INT,@p1 NVARCHAR(MAX),@p2 NVARCHAR(MAX),@QueryForDummyObject NVARCHAR(MAX),@ReplacementText NVARCHAR(4000)

		SELECT @EncryptedObject = [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = @objid AND [valclass] = 1
			BEGIN TRANSACTION
			SET @p = 1
			SET @p1= N'ALTER'+SPACE(1)+@objtype+SPACE(1)+ISNULL((@objschemaname+'.'),'')+@objname +SPACE(1)+@objtypicalstm;	
			SET @p1=@p1+REPLICATE('-',4000-LEN(@p1))			
			SET @p2 = REPLICATE('-',8000)
			SET @QueryForDummyObject = N'EXEC(@p1'
				WHILE @p <=CEILING(DATALENGTH(@EncryptedObject) / 8000.0)
				BEGIN
				SET @QueryForDummyObject=@QueryForDummyObject+N'+@f'
				SET @p =@p +1
				END
			SET @QueryForDummyObject=@QueryForDummyObject+')'				
			EXEC sp_executesql @QueryForDummyObject,N'@p1 NVARCHAR(4000),@f VARCHAR(8000)',@p1=@p1,@f=@p2
			SET @DummyObject=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = @objid and [valclass] = 1)			
			ROLLBACK TRANSACTION
			SET @ChunkNumber=1
			WHILE @ChunkNumber<=CEILING(DATALENGTH(@EncryptedObject) / 8000.0)
			BEGIN		
			SELECT @EncryptedChunk = SUBSTRING(@EncryptedObject, (@ChunkNumber - 1) * 8000 + 1, 8000)
			SELECT @DummyChunk = SUBSTRING(@DummyObject, (@ChunkNumber - 1) * 8000 + 1, 8000)
				IF @ChunkNumber=1
				BEGIN		
				SET @ReplacementText=N'CREATE'+SPACE(1)+@objtype+SPACE(1)+ISNULL((@objschemaname+'.'),'')+@objname +SPACE(1)+@objtypicalstm+REPLICATE('-',4000)			
				END
				ELSE
				BEGIN
				SET @ReplacementText=REPLICATE('-', 4000)
				END				
			SET @ChunkPiece = REPLICATE(N'A', (DATALENGTH(@EncryptedChunk) / 2))					
			SET @CompareChunksAtPosition=1
			WHILE @CompareChunksAtPosition<=DATALENGTH(@EncryptedChunk)/2
				BEGIN
				SET @ChunkPiece = STUFF(@ChunkPiece, @CompareChunksAtPosition, 1, NCHAR(UNICODE(SUBSTRING(@EncryptedChunk, @CompareChunksAtPosition, 1)) ^	(UNICODE(SUBSTRING(@ReplacementText, @CompareChunksAtPosition, 1)) ^ UNICODE(SUBSTRING(@DummyChunk, @CompareChunksAtPosition, 1)))))
				SET @CompareChunksAtPosition=@CompareChunksAtPosition+1
				END

			PRINT @ChunkPiece
			SET @ChunkNumber=@ChunkNumber+1
			END		
	END
	ELSE
	BEGIN
		PRINT 'Use a DAC Connection'
	END
END
ELSE
BEGIN
	PRINT 'Object not encrypted or not found'
END
SET QUOTED_IDENTIFIER OFF

GO

 

Answer 8

Hi Paul

Glad you were able to edit it for your needs, as you found the dummy objects for functions were not implemented.

Something like this should cover the other function types:

 

WHEN [type] IN ('FN') THEN  N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' 
WHEN [type] IN ('TF') THEN  N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END'
WHEN [type] IN ('IF') THEN  N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N' 

---

Jon

Answer 9

Thanks Jon, yes I added code for IF and TF but never tested it, I only tested FN which was all I needed, clearly your above will work wehere mine wont

I believe (from my very extensive and painstaking research before I resorted to fixing this) that what we have here is the only working solution on the web thats free (working for functions and sp's) ... 

 

cheers

Paul

Answer 10

I have modified Jon's post so it also supports functions correctly, jon's version gave a syntax error on functions because the dummy func sql was invalid

 

1. Connect using "admin:server\instance"  instead of "server\instance" (enable dac if you havent already, its an sp_reconfigure command)

2. create the below proc

3. exec SqlDecryptor 'dbo', 'function_or_stored_proc_name'

 

 

 

ALTER	-- CREATE
proc SqlDecryptor (@objschemaname nvarchar(255), @objname nvarchar(255))
AS

DECLARE @objid INT,@objtype NVARCHAR(50),@objtypicalstm NVARCHAR(4000),@objencrypted BIT

SELECT TOP 1 @objid=o,@objname = n,@objtype = t,@objtypicalstm=s,@objencrypted = (SELECT ([encrypted]) FROM syscomments WHERE [id] = x.o and colid = 1) 
FROM
(
SELECT object_id o, name n,
CASE WHEN [type] = 'P' THEN N'PROCEDURE' 
WHEN [type] = 'V' THEN 'VIEW' 
WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION' 
ELSE [type]
END t,
CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS' 
WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC' 
WHEN [type] IN ('FN','TF','IF') THEN N' () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' 
ELSE [type]
END s
FROM sys.all_objects WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF') 
AND name = @objname AND (SCHEMA_NAME([schema_id]) = COALESCE(@objschemaname,'dbo'))
--UNION ALL SELECT object_id,name,'TRIGGER',N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1' FROM sys.server_triggers WHERE name = @objname 
--UNION ALL SELECT object_id,name,'TRIGGER',N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1' FROM sys.triggers WHERE name = @objname
) x

--SELECT @objid,@objname,@objtype,@objtypicalstm,@objencrypted

SET NOCOUNT ON

IF @objencrypted <> 0
BEGIN
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
	BEGIN
		DECLARE @ChunkNumber INT,@ChunkPiece NVARCHAR(MAX),@CompareChunksAtPosition INT,@DummyChunk NVARCHAR(MAX),@DummyObject VARBINARY(MAX),@EncryptedChunk NVARCHAR(MAX),@EncryptedObject VARBINARY(MAX),@p INT,@p1 NVARCHAR(MAX),@p2 NVARCHAR(MAX),@QueryForDummyObject NVARCHAR(MAX),@ReplacementText NVARCHAR(4000)

		SELECT @EncryptedObject = [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = @objid AND [valclass] = 1
			BEGIN TRANSACTION
			SET @p = 1
			SET @p1= N'ALTER'+SPACE(1)+@objtype+SPACE(1)+ISNULL((@objschemaname+'.'),'')+@objname +SPACE(1)+@objtypicalstm;	
			SET @p1=@p1+REPLICATE('-',4000-LEN(@p1))			
			SET @p2 = REPLICATE('-',8000)
			SET @QueryForDummyObject = N'EXEC(@p1'
				WHILE @p <=CEILING(DATALENGTH(@EncryptedObject) / 8000.0)
				BEGIN
				SET @QueryForDummyObject=@QueryForDummyObject+N'+@f'
				SET @p =@p +1
				END
			SET @QueryForDummyObject=@QueryForDummyObject+')'				
			EXEC sp_executesql @QueryForDummyObject,N'@p1 NVARCHAR(4000),@f VARCHAR(8000)',@p1=@p1,@f=@p2
			SET @DummyObject=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = @objid and [valclass] = 1)			
			ROLLBACK TRANSACTION
			SET @ChunkNumber=1
			WHILE @ChunkNumber<=CEILING(DATALENGTH(@EncryptedObject) / 8000.0)
			BEGIN		
			SELECT @EncryptedChunk = SUBSTRING(@EncryptedObject, (@ChunkNumber - 1) * 8000 + 1, 8000)
			SELECT @DummyChunk = SUBSTRING(@DummyObject, (@ChunkNumber - 1) * 8000 + 1, 8000)
				IF @ChunkNumber=1
				BEGIN		
				SET @ReplacementText=N'CREATE'+SPACE(1)+@objtype+SPACE(1)+ISNULL((@objschemaname+'.'),'')+@objname +SPACE(1)+@objtypicalstm+REPLICATE('-',4000)			
				END
				ELSE
				BEGIN
				SET @ReplacementText=REPLICATE('-', 4000)
				END				
			SET @ChunkPiece = REPLICATE(N'A', (DATALENGTH(@EncryptedChunk) / 2))					
			SET @CompareChunksAtPosition=1
			WHILE @CompareChunksAtPosition<=DATALENGTH(@EncryptedChunk)/2
				BEGIN
				SET @ChunkPiece = STUFF(@ChunkPiece, @CompareChunksAtPosition, 1, NCHAR(UNICODE(SUBSTRING(@EncryptedChunk, @CompareChunksAtPosition, 1)) ^	(UNICODE(SUBSTRING(@ReplacementText, @CompareChunksAtPosition, 1)) ^ UNICODE(SUBSTRING(@DummyChunk, @CompareChunksAtPosition, 1)))))
				SET @CompareChunksAtPosition=@CompareChunksAtPosition+1
				END

			PRINT @ChunkPiece
			SET @ChunkNumber=@ChunkNumber+1
			END		
	END
	ELSE
	BEGIN
		PRINT 'Use a DAC Connection'
	END
END
ELSE
BEGIN
	PRINT 'Object not encrypted or not found'
END
SET QUOTED_IDENTIFIER OFF

GO

 

Answer 11

Hi Paul

Glad you were able to edit it for your needs, as you found the dummy objects for functions were not implemented.

Something like this should cover the other function types:

 

 

WHEN [type] IN ('FN') THEN  N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' 
WHEN [type] IN ('TF') THEN  N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END'
WHEN [type] IN ('IF') THEN  N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N' 

 

---

Jon

Answer 12

The latest version of this code to decrypt sql objects can be found at www.jongurgul.com/blog/sql-object-decryption/ I will continue to incorporate fixes and feature updates to this version so please always check for the newest version.