none
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
    Monday, September 27, 2010 8:49 AM

Answers

    • Proposed as answer by Naomi NModerator Monday, September 27, 2010 1:44 PM
    • Marked as answer by KJian_ Monday, October 4, 2010 8:46 AM
    Monday, September 27, 2010 9:51 AM
  • 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
    • Proposed as answer by Naomi NModerator Monday, September 27, 2010 1:51 PM
    • Marked as answer by KJian_ Monday, October 4, 2010 8:46 AM
    • Edited by Jon Gurgul Wednesday, January 2, 2013 3:39 PM
    Monday, September 27, 2010 1:50 PM
  • 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


    Wednesday, February 16, 2011 9:26 AM

All replies

  • 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/
    Monday, September 27, 2010 9:43 AM
    Answerer
    • Proposed as answer by Naomi NModerator Monday, September 27, 2010 1:44 PM
    • Marked as answer by KJian_ Monday, October 4, 2010 8:46 AM
    Monday, September 27, 2010 9:51 AM
  • 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
    • Proposed as answer by Naomi NModerator Monday, September 27, 2010 1:51 PM
    • Marked as answer by KJian_ Monday, October 4, 2010 8:46 AM
    • Edited by Jon Gurgul Wednesday, January 2, 2013 3:39 PM
    Monday, September 27, 2010 1:50 PM
  • 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
    Monday, October 11, 2010 10:04 AM
  • what error are you getting?


    Jon
    Monday, October 11, 2010 12:39 PM
  • 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


    Wednesday, February 16, 2011 9:26 AM
  • 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
    


     


    • Edited by BobTD Thursday, October 27, 2011 1:38 PM
    • Proposed as answer by Prakash Mishra Monday, January 16, 2012 4:12 PM
    Thursday, October 27, 2011 1:37 PM
  • 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
    • Proposed as answer by BobTD Tuesday, November 8, 2011 6:10 PM
    Thursday, October 27, 2011 2:19 PM
  • 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


    • Edited by BobTD Tuesday, November 8, 2011 6:10 PM
    Tuesday, November 8, 2011 6:09 PM
  • 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
    


     



    Monday, January 16, 2012 4:12 PM
  • 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

    Monday, January 16, 2012 4:13 PM
  • 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.

    • Proposed as answer by Naomi NModerator Sunday, September 23, 2012 6:08 PM
    • Edited by Jon Gurgul Wednesday, January 2, 2013 3:06 PM
    Sunday, September 23, 2012 3:52 PM
  • Hi Jon Gurgul!

      Can I decrypt a trigger? I tried to use your script but it fails with error message below:
    --
    SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(N'CREATE'+@ObjectNameStmTemplate,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1))))

    Msg 446, Level 16, State 9, Line 80
    Cannot resolve collation conflict for substring operation.


    Thanks
    Doria



    Doria

    Thursday, December 4, 2014 1:11 AM

  • Check out the following article that shows step by step method to decrypt SQL Server database objects: http://www.wikihow.com/Decrypting-Sql-Server-Database-Objects  
    But if manual methods fails to decrypt SQL Server database then you can try SQL Decryptor Tool: http://www.sqldecryptor.repair-sql.net/
    Saturday, March 26, 2016 6:00 AM
  • HI

    Please advice me why need to delete this stored procedure

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


    baskarlakshmi

    Tuesday, April 25, 2017 1:07 PM