August 5, 2009 at 1:32 pm
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)
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????
August 5, 2009 at 1:43 pm
August 7, 2009 at 7:31 am
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????
August 7, 2009 at 8:05 am
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
August 7, 2009 at 8:10 am
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?
August 7, 2009 at 8:39 am
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
August 7, 2009 at 9:07 am
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
May 3, 2010 at 11:44 am
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