Decryption Encrypted SP,View,Function in MSSQL

introduction

in SQL Server we can Create Stored procedure , View and Function with Encryption option by this option  no body can see the text of the object so a lot of Developer and DBA use this option to protect his Code but the problem down when any one of them forget what he write in this Encryption object it’s big problem because he can’t make Alter to this Encryption object but today we will learn how to troubleshot this  problem by creating stored procedure make Decryption to any Encrypted object it will show to me the text write in the encryption object

NOTE : this SP I’m not create it but I take it copy from the internet

1- Create Encryption Stored procedure

Create PROC getdatabase_Name

With Encryption option

as

Begin

Select name from sys.Databases where Database_id > 4

End

2- try to display code text

EXEC sp_helptext ‘getdatabase_Name’;

SQL server will tell you this Stored is Encrypted so you can’t see the Code text

3- Create SP to show Code text for any encryption object

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

4- no try to Execute your SP

EXCE  ShowDecrypted ‘getdatabase_Name’