Decrypt Stored Procedures on SQL Server

Have you ever encrypted stored procedures which had to be altered a few months later? Did you loose the source?
Don't panic, they can be decrypted using one of the two procedures below, I found these somewhere on the World Wide Web (credits go the the original authors).
Make sure you backup the database first!
Only use this for databases where you have the rights to decrypt the stored procedures (read the EULA if you're not sure).


-- PROCEDURE 1 ---

create PROCEDURE usp_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 encryption
execute( @resultsp)
GO





--- PROCEDURE 2 ---

if exists (select * from sysobjects where id = object_id('dbo.spDecryptSP') and sysstat & 0xf = 4)
drop procedure dbo.spDecryptSP
GO

Create procedure dbo.spDecryptSP
@spName varchar(50),
@Replace bit = 0
As
/*
Developer: Jonathan Spinks
Date: 24/07/03
Description: Decrypts SQL 2000 stored procedures

Inputs:
@spName = the name of the stored procedure you wish to decrypt
@Replace = 0 or 1. 0 if you wish to display the unencrypted sp
1 if you wish to replace the encrypted with the unencrypted sp

If this procedure returns "<@spName> is to large to decrypt"
this stored procedure can be modified to decrypt it.
Your starting point is to check out how many rows are returned
in the syscomments table for the stored procedures object_id.

If you just wish to display the sp then ensure that your result
pane is turned to 'Results in Text'.
In Query analyser (2k) Query => 'Results in Text'.

If your returned information looks 'cut short' first check
that your i_SQL query window is returning the entire string.
In Query analyser (2k) Tools => Options, Results tab,
Maximum characters per column = 8000.

Happy decrypting

Original idea: shoeboy
Adapted by: Joseph Gama Copyright © 1999-2002 SecurityFocus
Adapted Source: http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
Enhanced by: Jonathan Spinks Copyright © 2003 I.S. Software Developments. WASH
*/
DECLARE
@a1 nvarchar(4000), @b1 nvarchar(4000), @c1 nvarchar(4000), @d1 nvarchar(4000),
@a2 nvarchar(4000), @b2 nvarchar(4000), @c2 nvarchar(4000), @d2 nvarchar(4000),
@a3 nvarchar(4000), @b3 nvarchar(4000), @c3 nvarchar(4000), @d3 nvarchar(4000),
@a4 nvarchar(4000), @b4 nvarchar(4000), @c4 nvarchar(4000), @d4 nvarchar(4000),
@a5 nvarchar(4000), @b5 nvarchar(4000), @c5 nvarchar(4000), @d5 nvarchar(4000),
@a6 nvarchar(4000), @b6 nvarchar(4000), @c6 nvarchar(4000), @d6 nvarchar(4000),
@a7 nvarchar(4000), @b7 nvarchar(4000), @c7 nvarchar(4000), @d7 nvarchar(4000),
@a8 nvarchar(4000), @b8 nvarchar(4000), @c8 nvarchar(4000), @d8 nvarchar(4000),
@a9 nvarchar(4000), @b9 nvarchar(4000), @c9 nvarchar(4000), @d9 nvarchar(4000),
@a0 nvarchar(4000), @b0 nvarchar(4000), @c0 nvarchar(4000), @d0 nvarchar(4000),
@Perm nvarchar(4000),
@i int

if not exists(SELECT * FROM syscomments WHERE id = object_id(@spName))
Begin
print @spName +' cannot be found'
return
End

if exists(SELECT * FROM syscomments WHERE id = object_id(@spName) and encrypted = 0)
Begin
print @spName +' is not encrypted'
return
End

if (SELECT count(*) FROM syscomments WHERE id = object_id(@spName)) > 10
Begin
print @spName +' is to large to decrypt'
return
End

-- Get a list of the current permissions on the encrypted stored procedure
declare curPerm cursor fast_forward for
select '['+ u.name +']' as name, p.actadd, p.actmod
from dbo.syspermissions p inner join dbo.sysusers u
On p.grantee = u.uid
where p.id = object_id(@spName)
open curPerm

Set @Perm = ''
while 1 = 1
begin
declare @name sysname,
@actadd smallint,
@actmod smallint
fetch next from curPerm into @name, @actadd, @actmod
if @@fetch_status <> 0
break
-- For each permission in the list construct a GRANT or DENY command
if @actadd & 32 = 32
Set @Perm = @Perm +'GRANT EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
else if @actmod & 32 = 32
Set @Perm = @Perm +'DENY EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
end
close curPerm
deallocate curPerm

-- Get encrypted stored procedure
SELECT @a1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
SELECT @a2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
SELECT @a3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
SELECT @a4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
SELECT @a5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
SELECT @a6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
SELECT @a7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
SELECT @a8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
SELECT @a9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
SELECT @a0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10

-- Create blank stored procedure
SET @b1='ALTER PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
SET @b2=REPLICATE(N'-', len(@a2))
SET @b3=REPLICATE(N'-', len(@a3))
SET @b4=REPLICATE(N'-', len(@a4))
SET @b5=REPLICATE(N'-', len(@a5))
SET @b6=REPLICATE(N'-', len(@a6))
SET @b7=REPLICATE(N'-', len(@a7))
SET @b8=REPLICATE(N'-', len(@a8))
SET @b9=REPLICATE(N'-', len(@a9))
SET @b0=REPLICATE(N'-', len(@a0))

-- Wrap in transaction so original stored procedure can be restored
Begin transaction ReplaceSP

Execute (@b1 + @b2 + @b3 + @b4 + @b5 + @b6 + @b7 + @b8 + @b9 + @b0)

-- Get blank encrypted stored procedure
SELECT @c1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
SELECT @c2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
SELECT @c3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
SELECT @c4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
SELECT @c5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
SELECT @c6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
SELECT @c7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
SELECT @c8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
SELECT @c9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
SELECT @c0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10

If @Replace = 0
Rollback Transaction ReplaceSP
Else
Commit Transaction ReplaceSP

SET @b1='CREATE PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))

-- initalise the output variables
Set @d1 = ''
Set @d2 = ''
Set @d3 = ''
Set @d4 = ''
Set @d5 = ''
Set @d6 = ''
Set @d7 = ''
Set @d8 = ''
Set @d9 = ''
Set @d0 = ''

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a1)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d1 = @d1 + NCHAR(UNICODE(substring(@a1, @i, 1)) ^ (UNICODE(substring(@b1, @i, 1)) ^ UNICODE(substring(@c1, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a2)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d2 = @d2 + NCHAR(UNICODE(substring(@a2, @i, 1)) ^ (UNICODE(substring(@b2, @i, 1)) ^ UNICODE(substring(@c2, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a3)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d3 = @d3 + NCHAR(UNICODE(substring(@a3, @i, 1)) ^ (UNICODE(substring(@b3, @i, 1)) ^ UNICODE(substring(@c3, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a4)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d4 = @d4 + NCHAR(UNICODE(substring(@a4, @i, 1)) ^ (UNICODE(substring(@b4, @i, 1)) ^ UNICODE(substring(@c4, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a5)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d5 = @d5 + NCHAR(UNICODE(substring(@a5, @i, 1)) ^ (UNICODE(substring(@b5, @i, 1)) ^ UNICODE(substring(@c5, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a6)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d6 = @d6 + NCHAR(UNICODE(substring(@a6, @i, 1)) ^ (UNICODE(substring(@b6, @i, 1)) ^ UNICODE(substring(@c6, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a7)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d7 = @d7 + NCHAR(UNICODE(substring(@a7, @i, 1)) ^ (UNICODE(substring(@b7, @i, 1)) ^ UNICODE(substring(@c7, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a8)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d8 = @d8 + NCHAR(UNICODE(substring(@a8, @i, 1)) ^ (UNICODE(substring(@b8, @i, 1)) ^ UNICODE(substring(@c8, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a9)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d9 = @d9 + NCHAR(UNICODE(substring(@a9, @i, 1)) ^ (UNICODE(substring(@b9, @i, 1)) ^ UNICODE(substring(@c9, @i, 1))))
SET @i=@i+1
END

-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a0)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d0 = @d0 + NCHAR(UNICODE(substring(@a0, @i, 1)) ^ (UNICODE(substring(@b0, @i, 1)) ^ UNICODE(substring(@c0, @i, 1))))
SET @i=@i+1
END


if @Replace = 0
Begin
-- Output the unencrypted stored procedure to the screen
select @d1 as 'Unencrypted Stored Procedure'
select @d2
select @d3
select @d4
select @d5
select @d6
select @d7
select @d8
select @d9
select @d0
-- Output any permissions that were on the encrypted stored procedure
Select @Perm as 'Permissions'
End
Else
Begin
-- Drop the encrypted stored procedure
Exec('Drop Procedure '+ @spName)
-- Remove the 'WITH ENCRYPTION' command from the stored procedure
Set @d1 = Replace(@d1, 'WITH ENCRYPTION', '')
-- Create the unencrypted stored procedure
Exec(@d1 + @d2 + @d3 + @d4 + @d5 + @d6 + @d7 + @d8 + @d9 + @d0)
-- Apply any permissions that were on the encrypted stored procedure
Exec(@Perm)
End

Go