encrypt and decrypt stored procedures

Test

How to encrypt a SQL Server stored procedure

Here is an example of how to encrypt a SQL Server stored procedure. Before doing this, make sure your unencrypted version is stored in source control or somewhere else safe because once created with the "WITH ENCRYPTION" feature, you will no longer be able to access the version stored in SQL Server.

CREATE PROCEDURE dbo.MyStoredProcedure
WITH ENCRYPTION
AS
BEGIN
    SELECT 'my data'
END

Applies to: SQL Server 2005/2008

---

Grammer of CREATE PROCEDURE

• Syntax
• CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
•     [ { @parameter data_type }
•         [ VARYING ] [ = default ] [ OUTPUT ]
•     ] [ ,...n ]
• [ WITH
•     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
• [ FOR REPLICATION ]
• AS sql_statement [ ...n ]
  

复制代码

Arguments

owner

Is the name of the user ID that owns the stored procedure. owner must be either the name of the current user or the name of a role that a current user is a member of.

procedure_name

Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner. For more information, see Using Identifiers.

Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.

;number

Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory.

ENCRYPTION indicates that SQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. Note that obfuscated stored procedures can be reverse engineered because SQL Server must de-obfuscate procedures for execution. In SQL Server 2000, the obfuscated text is visible in the syscomments system table and may be susceptible to de-obfuscation attempts.

Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.

Note  During an upgrade, SQL Server uses the obfuscated comments stored in syscomments to re-create obfuscated procedures.

Test

This stored procedure will decrypt stored procedures, views or triggers that were encrypted using "with encryption". It is adapted from a 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 is 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.

1. create  PROCEDURE sp_decrypt_sp (@objectName varchar(50))
   
2. AS
   
3. DECLARE  @OrigSpText1 nvarchar(4000),  @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
   
4. declare  @i int , @t bigint
   
5. 
   
6. --get encrypted data
   
7. SET @OrigSpText1=(SELECT ctext FROM syscomments  WHERE id = object_id(@objectName))
   
8. SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)
   
9. EXECUTE (@OrigSpText2)
   
10. 
    
11. SET @OrigSpText3=(SELECT ctext FROM syscomments  WHERE id = object_id(@objectName))
    
12. SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
    
13. 
    
14. --start counter
    
15. SET @i=1
    
16. --fill temporary variable
    
17. SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
    
18. 
    
19. --loop
    
20. WHILE @i<=datalength(@OrigSpText1)/2
    
21. BEGIN
    
22. --reverse encryption (XOR original+bogus+bogus encrypted)
    
23. SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
    
24.                                 (UNICODE(substring(@OrigSpText2, @i, 1)) ^
    
25.                                 UNICODE(substring(@OrigSpText3, @i, 1)))))
    
26. SET @i=@i+1
    
27. END
    
28. --drop original SP
    
29. EXECUTE ('drop PROCEDURE '+ @objectName)
    
30. --remove encryption
    
31. --preserve case
    
32. SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
    
33. SET @resultsp=REPLACE((@resultsp),'With Encryption', '')
    
34. SET @resultsp=REPLACE((@resultsp),'with encryption', '')
    
35. IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
    
36.   SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
    
37. --replace Stored procedure without enryption
    
38. execute( @resultsp)
    
39. 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 512, Level 16, State 1, Procedure sp_decrypt_sp, Line 7.
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

Karl C writes: I got the same message as Joakim M. but upon further investigationI found that this happens only when stored procedures exceed 4000 characters.When this happens, SQL Server stores the procedure across multiple rows so you get theerror 'subquery returne more than 1 row'. To get around that you can change the statement

• SELECT ctext FROM syscomments WHERE id = object_id(@objectName
  

复制代码

to

• SELECT top 1 ctext FROM syscomments WHERE id = object_id(@objectName order by colid
  

复制代码

That will get you the first part of the stored procedure, which can't be created because it is missing the endpart and is not a valid syntax but you can print @resultsp out to see it.

---

DECRYPT SQL SERVER 2000 STORED PROCEDURES, VIEWS AND TRIGGERS (WITH EXAMPLES)

This SP will decrypt Stored Procedures, Views or Triggers that were encrypted using "with encryption"There are 2 versions: one for SP's only and the other one for SP's, triggers and viewsversion 1:INPUT: object name (stored procedure, view or trigger)version 2:INPUT: object name (stored procedure, view or trigger), object type('T'-trigger, 'P'-stored procedure or 'V'-view) Original idea: shoeboy <shoeboy@adequacy.org>Copyright © 1999-2002 SecurityFocus adapted by Joseph GamaPlanet Source Code, my employer and myself are not responsible for the use of this codeThis code is provided as is and for educational purposes onlyPlease test it and share your results

DECRYPT_SQ1062217152002.zip(3.03 KB, 下载次数: 0, 售价: 3 金钱)