Solutions for SQL stored procedures

1, and did not delete the original storage reconstruction, only in the console made an output, copy out very inconvenient.
2. For large-length encrypted data will be decrypted failed.
Change this stored procedure with these two questions.

The first thing you need to know about the DAC is to do the prep work, which refers to the database tutorial dedicated Administrator connection, a special kind of diagnostic connection for the administrator.
When you know, you have to open it first, take SQL2008 as an example:
Right-click on "Object Browser" to find "Facets", click, as shown:

Locate "Sruface area Configuration", select "Remotedacenabled", and set to true:


Then the DAC login, cmd mode to knock down the following command, not clear the principle of their own research:
Sqlcmd-a-S 192.168.1.101-u sa-p 123456
Open the database you want to process under the command prompt:
>use TEST
>go

Ready, copy the search to get stored procedures (there are some grammatical problems, make minor adjustments), generate decryption memory, and then we prepare two encrypted stored procedures, one of the larger length (can be a loop to create a 500-line memory, this article is no longer available), verify that the conclusion that The short stored procedure quickly decrypted successfully and output, but the larger length of the decryption failed.
Let's look at how it's decrypted:
Let's see.
Select @maxColID = max (Subobjid), @intEncrypted = Imageval from sys.sysobjvalues WHERE ObjID = object_id (@procedure)
This means that the encrypted data is stored in the "sys.sysobjvalues" table, and its contents are stored in the "Imageval" field.

Once you know the encrypted data, you have to decrypt it, which defines 4 key fields:
DECLARE @real_01 nvarchar (max)
DECLARE @fake_01 nvarchar (max)
DECLARE @fake_encrypt_01 nvarchar (max)
DECLARE @real_decrypt_01 nvarchar (max)
The contents of the original encrypted data, the CREATE statement of the original encrypted stored procedure, the encrypted data of the fake stored procedure constructed by oneself, and the stored procedure after the final decryption are respectively referred to.
The method is to @real_01, @fake_encrypt_01, @real_decrypt_01 by bit, and why is this so handled, the principle is unknown!!!
While @intProcSpace <= (datalength (@real_01)/2)
BEGIN
--xor Real & fake & fake encrypted
SET @real_decrypt_01 = Stuff (@real_decrypt_01, @intProcSpace, 1, NCHAR (UNICODE (substring (@real_01, @intProcSpace, 1)) ^ (UNICODE (substring (@fake_01, @intProcSpace, 1)) ^unicode (substring (@fake_encrypt_01, @intProcSpace, 1)))
SET @intProcSpace = @intProcSpace +1
End
As a matter of fact, the encrypted stored procedure has been decrypted.

The following there is a large section of the statement did not study carefully, but basically using sp_helptext content output, methods to compare the rope, and did not achieve the effect we want, we will change a method for output.
Basically it is so simple, in addition to the principle is not clear, basically has reached the requirements, the next to solve the two issues raised.
The first is the length of the problem, why the length of a large decryption failed to see the definition of @real_decrypt_01 and initialize @real_decrypt_01 statement:
DECLARE @real_decrypt_01 nvarchar (max)
SET @real_decrypt_01 = replicate (N ' A ', (datalength (@real_01)/2))
At first glance, it's OK, but we use Len (@real_decrypt_01) output to see that the maximum output length is 4000, the nvarchar length of the problem may arise, theoretically nvarchar (MAX) supports the size of 2G. Why this is happening has not been studied, but someone has given a workaround for display conversions:
SET @real_decrypt_01 = Replicate (CONVERT (NVARCHAR (MAX), N ' A '), (datalength (@real_01)/2))
The other several are also the reason, the correction after the rerun, problem resolution, the length of the larger stored procedures also decrypted successfully.

The first problem solved, how can the convenient output, the test deletes the reconstruction, but did not succeed, then uses the simplest method, uses the xp_cmdshell to output the content to the text.
Create a physical table to store the decrypted data:

  
   
   

    
    
   CREATE TABLE [dbo]. [Sql_decode] (
   
    
    
    [ID] [int] IDENTITY (1,1) not NULL,
   
    
    
    [SQLText] [nvarchar] (max) Not NULL,
   
    
    
CONSTRAINT [ID] PRIMARY KEY CLUSTERED 
   
    
    
(
   
    
    
    [ID] ASC
   
    
    
)
   
    
    
) on [PRIMARY]
   
    
    

   
    
    
Go
  
   
   

It then establishes a stored procedure, traverses all encrypted stored procedures, invokes the decryption stored procedure for decryption, decrypts the output:

  
  

   
   

     CREATE PROCEDURE [dbo]. [Decode_database] 
    
 as 
    
 set NOCOUNT on 
    
 BEGIN 
    
 
    
 DECLARE @PROC_NAME VARCHAR (256) 
    
 Set @PROC_NAME = ' 
    
 DECLARE @ROWS INT 
    
 DECLARE @TEMP TABLE ( 
    
 NAME VARCHAR (256) 
    
) 
    
 INSERT into @TEMP sele CT NAME from sysobjects WHERE TYPE = ' P ' 
    
 and NAME not in ( 
    
 ' decode_database ', ' Decode_proc ' 
    
 
    
 SET @ROWS = @ @ROWCOUNT 
    
 While @ROWS > 0 
    
 BEGIN 
    
 SELECT @PROC_NAME = NAME from ( 
    
 SELECT row_number () by name as ROW, NAME from @TEMP 
    
) T 
    
 WHERE ROW = @ROWS 
    
 EXEC [Decode_proc] @PROC_NAME 
    
 PRINT @PROC_NAME 
    
 SET @ROWS = @ROWS-1 br> End 
    
 
    
 return 
    
 
    
 EXEC master. xp_cmdshell ' bcp ' SELECT [SQLText] from test.dbo. [Sql_decode] "queryout c:decode.txt-c-t-s pc2011043012juj ' 
    
 End 
    
 
    
 
    
 Go 
   
  
  

Finally, the reformed stored procedures:

  
  

   
   CREATE PROCEDURE [dbo]. [Decode_proc] (
   
   
@PROC_NAME SYSNAME = NULL
   
   
)
   
   
As
   
   
SET NOCOUNT on
   
   

   
   
DECLARE @PROC_NAME_LEN INT--Stored procedure name length
   
   
DECLARE @MAX_COL_ID SMALLINT--Maximum column ID
   
   
SELECT @MAX_COL_ID = MAX (Subobjid) from sys.sysobjvalues WHERE ObjID = object_id (@PROC_NAME) GROUP by Imageval
   
   

   
   
SELECT @PROC_NAME_LEN = datalength (@PROC_NAME) + 29
   
   
DECLARE @REAL_01 NVARCHAR (MAX)--Real encrypted stored procedure data
   
   
DECLARE @FACK_01 NVARCHAR (MAX)--modified to fake stored procedures, length (40003-There is a process name length), the principle is unclear?
   
   
DECLARE @FACK_ENCRYPT_01 NVARCHAR (MAX)--pseudo-encryption store over-street data
   
   
DECLARE @REAL_DECRYPT_01 NVARCHAR (MAX)-The final decrypted data, initialized to half of the original encryption length "A", the principle is unclear?
   
   

   
   
SET @REAL_01 = (
   
   
SELECT imageval from sys.sysobjvalues WHERE ObjID = object_id (@PROC_NAME) and Valclass = 1 and Subobjid = 1
   
   
)
   
   

   
   
DECLARE @REAL_DATA_LEN BIGINT
   
   
SET @REAL_DATA_LEN = datalength (@REAL_01)
   
   
--print @REAL_DATA_LEN
   
   

   
   
DECLARE @FACK_LEN BIGINT
   
   
SET @FACK_LEN = @REAL_DATA_LEN * 10--Modification: false length magnified 10 times times in the original true data length
   
   

   
   
--Convert the nvarchar display to nvarchar (MAX), or you will only be able to produce 4K length
   
   
SET @FACK_01 = ' ALTER PROCEDURE ' + @PROC_NAME + ' with encryption as ' + REPLICATE (CONVERT (NVARCHAR (MAX), '-'), @FACK_LEN -@PROC_NAME_LEN)
   
   
--print ' @FACK_01 = ' + STR (LEN (@FACK_01))
   
   
EXECUTE (@FACK_01)
   
   
SET @FACK_ENCRYPT_01 = (
   
   
SELECT imageval from sys.sysobjvalues WHERE ObjID = object_id (@PROC_NAME) and Valclass = 1 and Subobjid = 1
   
   
)
   
   

   
   
SET @FACK_01 = ' CREATE PROCEDURE ' + @PROC_NAME + ' with encryption as ' + REPLICATE (CONVERT (VARCHAR (MAX), '-'), @FACK_LEN -@PROC_NAME_LEN)
   
   
SET @REAL_DECRYPT_01 = REPLICATE (CONVERT (NVARCHAR (MAX), N ' A '), (datalength (@REAL_01)/2))
   
   
--print ' len (@REAL_DECRYPT_01) = ' + STR (len (@REAL_DECRYPT_01))
   
   

   
   

   
   
--to vary or manipulate @REAL_01, @FACK_01, @REAL_DECRYPT_01 by bit.
   
   
DECLARE @INT_PROC_SPACE BIGINT
   
   
SET @INT_PROC_SPACE = 1
   
   
While @INT_PROC_SPACE <= (datalength (@REAL_01)/2)
   
   
BEGIN
   
   
SET @REAL_DECRYPT_01 = STUFF (
   
   
@REAL_DECRYPT_01,
   
   
@INT_PROC_SPACE,
   
   
1,
   
   
NCHAR (Unicode (SUBSTRING (@REAL_01, @INT_PROC_SPACE, 1)) ^ (Unicode (SUBSTRING (@FACK_01, @INT_PROC_SPACE, 1)) ^ Unicode ( SUBSTRING (@FACK_ENCRYPT_01, @INT_PROC_SPACE, 1)))
   
   
)
   
   
SET @INT_PROC_SPACE = @INT_PROC_SPACE + 1
   
   
End
   
   

   
   
--Remove with encryption
   
   
SET @REAL_DECRYPT_01 = REPLACE (@REAL_DECRYPT_01, ' with encryption ', ')
   
   
INSERT into [Sql_decode] VALUES (@REAL_DECRYPT_01)
   
   

   
   
--print ' @REAL_DECRYPT_01 = ' + @REAL_DECRYPT_01
   
   
--print ' len (@REAL_DECRYPT_01) = ' + STR (len (@REAL_DECRYPT_01))
   
   

   
   
--Delete the original stored procedure
   
   
SET @FACK_01 = ' DROP PROCEDURE ' + @PROC_NAME
   
   
EXEC (@FACK_01)
   
   

   
   
Go
  
  

So far, the decryption process is complete, command line mode to run:
>exec [Decode_database]
>go
In the C-packing directory, the decrypted stored procedure text generation succeeds.
Do not forget to open xp_cmdshell permission before running, same as open DAC:


or the command line mode to knock the following command:
>sp_configure ' Show advanced options ', 1
>reconfigure
>go
>sp_configure ' xp_cmdshell ', 1
>reconfigure
>go