Recreating an encyrpted Stored Procedure

  • Cannot view, modify or script stored procedures in a Database

    Property TextHeader is not available for StoredProcedure '[dbo].[CRY_CIFFilter]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476%5B/code%5D

    i suspect the stored procedures are encrypted and it is not a permissions issue.

    How can I modify or script out these encryped stored procedures????

  • Thank you! I tried this method and it dropped my stored procedure!? Here is what I did:

    I downloaded the script to create the stored procedure then created it:

    USE [PBSA_decrypt]

    GO

    /****** Object: StoredProcedure [dbo].[DECRYPTSP2K] Script Date: 08/07/2009 09:23:27 ******/

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    CREATE PROCEDURE [dbo].[DECRYPTSP2K] (@objName varchar(50))

    --INPUT: object name (stored procedure,

    --

    -- view or trigger)

    --Original idea: shoeboy

    --Copyright © 1999-2002 SecurityFocus

    --adapted by Joseph Gama

    --Planet Source Code, my employer and my

    --

    -- self are not responsible for the use

    -- of

    -- this code

    --This code is provided as is and for ed

    --

    -- ucational purposes only

    --Please test it and share your results

    AS

    DECLARE @a nvarchar(4000), @b-2 nvarchar(4000), @C nvarchar(4000), @d nvarchar(4000), @i int, @t bigint

    --get encrypted data

    SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))

    SET @b-2='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)

    EXECUTE (@b)

    --get encrypted bogus SP

    SET @C=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))

    SET @b-2='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)

    --start counter

    SET @i=1

    --fill temporary variable

    SET @d = replicate(N'A', (datalength(@a) / 2))

    --loop

    WHILE @i0

    SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')

    --replace SP

    execute( @d)

    I then tried exec sp_helptext mystoredprocedure and got this:

    The text for object 'mystoredprocedure' is encrypted.

    I then ran this:

    exec decryptsp2k 'mystoredprocedure'

    and it executed successfully.

    I then ran exec sp_helptext mystoredprocedure and it cannot find the stored procedure.. in fact, it is gone!!!

    Does anyone know what I did wrong????

  • Not sure you did anything wrong..

    I noticed that the code seemed to be for SQL 2000 are you sure it works for 2005?

    CEWII

  • I do not have a SQL Server 2000 instance that I can try this in..

    So there is no way to de-cyrpt these stored procedures in a 2005 database?

  • I don't know, I was commenting that the code seems to be for SQL 2000, as far as whether it will work with 2005 is a question given that 2005 includes stronger encryption routines and I would be surprised if the engine didn't use them itself. The encryption in 2000 was marginal at best, not so sure about 2005.

    CEWII

  • The method to decrypt a proc in 2000 does not work at all in SQL 2005.

    Google for SQL 2005 stored procedure decryption. You should be able to find a tool that does it, maybe even a free one.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello-

    It happends to me as well. The stored procedure I wanted to decrypt was deleted after I execute exec DECRYPTSP2K 'Test'. Did you ever get it worked? Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

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