DESINCRIPTAR STORE PROCEDURE SQL 2005

Hola Cangri.

Yo tuve un problema parecido hace unos años y utilicé algo, no estoy
seguro si es lo que te paso a continuación, te sugiero que hagas la
prueba en una base de datos aparte para evitar algún problema, no lo
pruebo por estar full en éste momento.

No estaría mal que pases un comentario luego.

Son dos SP, pruebalos, como te digo no recuerdo como es.

Espero que te sea de ayuda.


PRIMERO=SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE DECRYPT2K (@objName varchar(50), @type char(1) )
AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d
nvarchar(4000), @i int, @t bigint, @tablename varchar(255), @trigtype
varchar(6)
SET @type=UPPER(@type)
IF @type='T'
BEGIN
SET @tablename=(SELECT sysobjects_1.name
FROM dbo.sysobjects INNER JOIN
dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj = sysobjects_1.id
WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
SET @trigtype=(SELECT CASE WHEN dbo.sysobjects.deltrig > 0 THEN 'DELETE'
WHEN dbo.sysobjects.instrig > 0 THEN 'INSERT'
WHEN dbo.sysobjects.updtrig > 0 THEN 'UPDATE' END
FROM dbo.sysobjects INNER JOIN
dbo.sysobjects sysobjects_1 ON dbo.sysobjects.parent_obj =
sysobjects_1.id
WHERE (dbo.sysobjects.type = 'TR') AND (dbo.sysobjects.name = @objName))
END
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b=case @type
WHEN 'S' THEN 'ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS
'+REPLICATE('-', 4000-62)
WHEN 'V' THEN 'ALTER VIEW '+ @objName +' WITH ENCRYPTION AS SELECT
dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', 4000-150)
WHEN 'T' THEN 'ALTER TRIGGER '+@objName+' ON '+ @tablename+' WITH
ENCRYPTION FOR '+@trigtype+' AS PRINT ''a'''+REPLICATE('-', 4000-150)
END
EXECUTE (@b)
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b=case @type
WHEN 'S' THEN 'CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS
'+REPLICATE('-', 4000-62)
WHEN 'V' THEN 'CREATE VIEW '+ @objName +' WITH ENCRYPTION AS SELECT
dbo.dtproperties.* FROM dbo.dtproperties'+REPLICATE('-', 4000-150)
WHEN 'T' THEN 'CREATE TRIGGER '+@objName+' ON '+ @tablename+' WITH
ENCRYPTION FOR '+@trigtype+' AS PRINT ''a'''+REPLICATE('-', 4000-150)
END
SET @i=1
SET @d = replicate(N'A', (datalength(@a) / 2))
WHILE @i<=datalength(@a)/2
BEGIN
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END
IF @type='S'
EXECUTE ('drop PROCEDURE '+ @objName)
ELSE
IF @type='V'
EXECUTE ('drop VIEW '+ @objName)
ELSE
IF @type='T'
EXECUTE ('drop TRIGGER '+ @objName)
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
execute( @d)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

PRIMERO
SEGUNDO=SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE DECRYPTSP2K (@objName varchar(50))
AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d
nvarchar(4000), @i int, @t bigint
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS
'+REPLICATE('-', 4000-62)
EXECUTE (@b)
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS
'+REPLICATE('-', 4000-62)
SET @i=1
SET @d = replicate(N'A', (datalength(@a) / 2))
WHILE @i<=datalength(@a)/2
BEGIN
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END
EXECUTE ('drop PROCEDURE '+ @objName)
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
execute( @d)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SEGUNDO=
CANGRI escribió:

HOLA AMIGOS :
TENGO UN GRAN PROBLEMA QUE HASTA AHORA NADIE ME A PODIDO AYUDAR , TENGO EL
SIGUIENTE PROBLEMA ; TENGO UN STORE PROCEDURE QUE ESTA ENCRIPTADO :

CREATE STORE PROCEDURE PA_RECAUDA
@YEAR INT
WITH ENCRYPTION
..
ETC

LA CONSULTA ES COMO PUEDO DESINCRIPTAR MI SP ... ESPERO SU AYUDA AMIGOS
URGENTE.

SALUDOS