Decrypt Encrypted stored procedures

Http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci837799,00.html



This SP would decrypt Stored procedures, views or triggers that were the using "with encrypted". It is adapted from script by Joseph Gama and Shoeboy. There are two versions:one for SP ' s only and the other one for SP ' s, triggers and views. For version 1, the input was object name (stored procedure, view or trigger), and for version 2, the input is Object name ( Stored procedure, view or trigger), Object type (' T '-trigger, ' P '-stored procedure or ' V '-view). From Planetsourcecode.com.


Create PROCEDURE sp_decrypt_sp (@objectName varchar) asdeclare @OrigSpText1 nvarchar (4000), @OrigSpText2 nvarchar ( 4000), @OrigSpText3 nvarchar (4000), @resultsp nvarchar (4000) declare @i int, @t bigint--get DataSET @OrigSpText 1 = (SELECT ctext from syscomments WHERE id = object_id (@objectName)) SET @OrigSpText2 = ' ALTER PROCEDURE ' + @objectName + ' WIT H encryption as ' +replicate ('-', 3938) EXECUTE (@OrigSpText2) SET @OrigSpText3 = (SELECT ctext from syscomments WHERE id = obj ECT_ID (@objectName)) SET @OrigSpText2 = ' CREATE PROCEDURE ' + @objectName + ' with encryption as ' +replicate ('-', 4000-62)-- Start CounterSet @i=1--fill temporary Variableset @resultsp = replicate (N ' A ', (datalength (@OrigSpText1)/2))--loopwhile @i<=datalength (@OrigSpText1)/2begin--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 (subs Tring (@OrigSpText3, @i, 1))) SET @i=@I+1end--drop Original Spexecute (' Drop PROCEDURE ' + @objectName)--remove encryption--preserve caseset @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 Enryptionexecute (@resultsp) go



Reader Feedback


Joakim M. Writes:i tried this script with mixed results. It works for some encrypted procedures, but for others I get error meassages like:

Server:msg, level, State 1, Procedure sp_decrypt_sp, line 7.Subquery returned more than 1 value. This isn't permitted when the subquery follows =,!=, <= and when the >= is subquery as a used Sion.

Karl C writes:i got the same message as Joakim M. But upon further investigation I found that this happens only when stor Ed procedures exceed 4000 characters. When this is happens, SQL Server stores the procedure across multiple rows so to the error ' subquery Returne more than 1 Row '. To get around, can change the statement

Select Ctext from syscomments where id = object_id (@objectNameto select top 1 ctext from syscomments where id = object_id ( @objectName ORDER BY Colid

That'll get you the stored procedure which can ' t being created because it's missing the end part and is Not a valid syntax but your can print @resultsp out to the it.

For more information

Feedback:e-mail the editor with your thoughts about this tip. More tips:hundreds of free SQL Server tips and scripts. Tip contest:have A SQL Server tip to offer your fellow DBAs and developers? The best tips submitted'll receive a cool prize--Submit your Tip today! Best Web links:sql Server tips, tutorials, scripts, and more. Forums:ask your technical SQL Server Questions--or help out your peers by answering them--in our active forums. Ask the Experts:our SQL, Database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are Swer your toughest questions.