Store Procedure Encryption

[funcrecall12]

Im planning to encrypt all my stored procedure,,
but I have read this

"Encrypting Procedure Definitions
If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.

After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator."

how about if there is a problem in the storeprocedure and I have to redefine the procedure.. Can I drop it and create a new one??

 

[Sabnac]

Yes, just keep your sp scripted in a safe place and rerun modified script for update.

 

[rt63]

It is a good practice to keep proper version control of your source code but if for some reason you have lost it, there is code available on the web to decrypt the procedures as I got to know recently. See this thread

thread183-419034


RT

 

[osjohnm]

Hi funrecall12

Try the following stored proc which will decrypt an encrypted stored proc, just pass it the stored procs name.

create PROCEDURE sp_decrypt_sp (@objectName varchar(50))
AS
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare @i int , @t bigint

--get encrypted data
SET @OrigSpText1=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)
EXECUTE (@OrigSpText2)

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)

--start counter
SET @i=1
--fill temporary variable
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))

--loop
WHILE @i<=datalength(@OrigSpText1)/2
BEGIN
--reverse encryption (XOR original+bogus+bogus encrypted)
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
--drop original SP
EXECUTE ('drop PROCEDURE '+ @objectName)
--remove encryption
--preserve case
SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
SET @resultsp=REPLACE((@resultsp),'With Encryption', '')
SET @resultsp=REPLACE((@resultsp),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
--replace Stored procedure without enryption
execute( @resultsp)
GO

-- eg....exec sp_decrypt_sp 'GET_ORDER_DETAILS'

Hope this helps

John

 

[ConfusedPerson]

Hi
I was interested in the web link for being able to unencrypt stored procedure definitions, but it doesn't appear to work. Any chance of getting the URL? Many Thanks.

 

[osjohnm]

Kussar

Go the follwoing thread Thread183-343538
which contains sp_decrypt_sp again or have a look at

http://www.sqlservercentral.com

You will have to register to use the sight but you will find some decrypt procedures there under scripts.

John

 

[ConfusedPerson]

Many thanks