mssqlserver.com: FAQ: How can I decrypt a SQL Server stored-procedure?

Home

Articles

Backup

Books

Certification

FAQ

Products

Replication

Scripts

Seminars

Training

TSQL

MSDN Fourms

Philippine SSUG

Fort Worth SSUG

Oklahoma City SSDG

Resume

MHS Enterprises

BlowFrog Software

FilAm Software

AcrylicAcetate.com

Bargain Humidors

Western Humidor

How can I decrypt a SQL Server stored-procedure?

Here is the code for SQL 6.5

/************************************************/
/* REVISED REC'D 5/21/98 */
/* sp_decrypt_object Tom Sager 01/26/98 */
/* */
/* Decrypts objects (views, procedures & trigs) */
/* created with the WITH ENCRYPTION option. */
/* */
/* Uses the encrypt() built-in function to find */
/* a plaintext string that encrypts to the same */
/* value as stored in the text column of the */
/* syscomments table. */
/* */
/************************************************/
create proc sp_decrypt_object
(@objname varchar(30))
WITH ENCRYPTION
as

SET NOCOUNT ON

declare @errmsg varchar(80)
declare @encrtext varchar(255)
declare @decrtext varchar(255)
declare @testtext varchar(255)
declare @printline varchar(255)
declare @textlen int
declare @lup int
declare @match char(1)
declare @testchar smallint
declare @begblk smallint
declare @endblk smallint

if (select count(*)
from sysobjects
where name = @objname) = 0
begin
select @errmsg = 'Object '
+@objname
+' not found in database '
+DB_NAME()
print @errmsg
return 1
end

if (select count(*) from sysobjects t1,
syscomments t2
where t1.name = @objname
and t1.id = t2.id
and t2.texttype & 4 != 0) = 0
begin
select @errmsg = 'Object '
+@objname
+' is not encrypted in database '
+DB_NAME()
print @errmsg
return 1
end

DECLARE comments_cursor CURSOR for
select t1.text
from syscomments t1,
sysobjects t2
where t1.id = t2.id
and t2.name = @objname
order by t1.colid

OPEN comments_cursor

FETCH NEXT FROM comments_cursor
INTO @encrtext

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @decrtext = REPLICATE(' ', 255)
select @textlen = DATALENGTH(@encrtext)
select @lup = 1
select @match = 'n'
while (@lup <= @textlen)
begin
select @testchar = 0
select @match = 'n'
while (@match = 'n')
begin
select @decrtext =
STUFF(@decrtext,@lup,1,CHAR(@testchar))
select @testtext = encrypt(@decrtext)
if ASCII(SUBSTRING(@testtext,@lup,1)) =
ASCII(SUBSTRING(@encrtext,@lup,1))
begin
select @match = 'y'
end
select @testchar = @testchar + 1
if (@testchar > 255)
begin
print 'Error...no match found'
return 1
end
end
select @lup = @lup + 1
end
select @begblk = 1
select @endblk = 1
while (@endblk <= @textlen)
begin
if (substring(@decrtext,@endblk,1) = 0x0a)
begin
select @printline = @printline +
SUBSTRING(@decrtext
,@begblk
,@endblk-@begblk+1)
print @printline
select @begblk = @endblk + 1
select @endblk = @begblk
select @printline = NULL
end
select @endblk = @endblk + 1
end
select @printline = @printline +
SUBSTRING(@decrtext
,@begblk
,@endblk-@begblk+1)
END
FETCH NEXT FROM comments_cursor INTO @encrtext
END
print @printline
CLOSE comments_cursor

DEALLOCATE comments_cursor
GO

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.