How can I unencrypted a stored procedure

  • Mai Nguyen-460836

    Old Hand

    Points: 371

    I got few stored procedures With Encryption for the database that I converted to SQL Server 2000.   I need to access those stored procedures.   How can I un-encrypted them?

  • mdaniel

    SSCarpal Tunnel

    Points: 4586

    Try this:

    http://www.mssqltips.com/tip.asp?tip=1046

  • SQL_ABD

    SSCertifiable

    Points: 5722

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    If it's SQL Server 2000, there's no better tool than:

    dSQLSRVD - dOMNAR's SQL Server SysComments Decryptor

    It doesn't work on SQL Server 2005, which requires an admin connection, etc., but it's great for SQL Server 2000.

    K. Brian Kelley
    @kbriankelley

  • Ron Aughenbaugh

    SSC Eights!

    Points: 975

    IF I am not mistaken I thought the unencrypted version is stored in one of the sys tables. Unfortunately I can't remeber which one.

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    Ron Aughenbaugh (11/26/2007)


    IF I am not mistaken I thought the unencrypted version is stored in one of the sys tables. Unfortunately I can't remeber which one.

    Unfortunately, no. In syscomments it is encrypted. I believe in SQL Server 2005 it's encrypted as well. SQL Server 2005 makes it more difficult because you've got to come in via DAC to be able to decrypt.

    K. Brian Kelley
    @kbriankelley

  • JMSM

    SSCertifiable

    Points: 5440

    Hello,

    Thanks a lot Brian, the tool that you told me (dOMNAR's SQL Server SysComments Decryptor) was very usefull.

    Thanks and regards,

    JMSM 😉

  • Jacob Luebbers

    SSCarpal Tunnel

    Points: 4796

    You can get "SQL Server 2005 Code Recovery" from here[/url] that does the 2005 DAC decrypt for you in a no-brain manner if you don't mind paying a little.

    Regards,

    Jacob

  • Zahran

    SSCommitted

    Points: 1614

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    Have you tried turning on profiler and running the procedure?

    Often enough - Profiler will capture the unencrypted version of the procedure. No fuss, no muss.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • K. Brian Kelley

    SSC Guru

    Points: 114445

    Profiler won't show you the CREATE PROCEDURE statement. You can get the execution of the procedure, but not the definition. If you try and run Profiler you'll get something akin to "-- Encrypted Text" for the TextData column.

    K. Brian Kelley
    @kbriankelley

  • ..:: Eric ::..

    SSC Rookie

    Points: 34

    Hello,

    I am using SQL 2000 and I've same Problem here. and yes the script does work to solve the encrypted SP.

    still there are some problem have not been solved. they are more than 4000 length SP script can't be decrypted.

    i wonder if there are another solution for this kind of problem.

    regards,

    Eric

  • johnycruen

    Valued Member

    Points: 57

    Hi,

    Recently, I read one article about SQL Decryptor which explains the SQL Server encryption and decryption process. you can take help from it to get points clear about sql decryption for the encrypted database which included encryption of stored procedures, views, rules, etc.

    http://www.articlesbase.com/software-articles/sql-decryptor-finest-solution-to-de-encrypt-sql-database-5902847.html

  • jack.rider.rider

    Grasshopper

    Points: 24

    No problem whatsoever if you want to decrypt stored procedures, tables or any other database in SQL server then you can just get the perfect solution from: http://tech.groups.yahoo.com/group/DecryptSQLServer2008/[/url]

  • Russf

    SSC Enthusiast

    Points: 153

    Red Gate's SQL Compare will un-encrypt an "encrypted" stored procedure.

    1) Start SQL Compare.

    2) Specify the database , containing the encrypted procedure(s) , as both the source and target (can pick a different db if you wish as the target).

    3) Click COMPARE.

    4) In the results window, click on the stored procedure that you are interested in and BAZINGA..... there is the source code.

    I was using version 8.1.0.360 in case your experience varies. I was using SQL 2005 . I tried on SQL 2000 and it also worked.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply