Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.

Neither do I have older 2005 backup of the database.

I do not have decrypted procedures to restore manually.

Is there any way to just copy/move them over?

share|improve this question

migrated from stackoverflow.com Aug 4 '12 at 14:52

This question came from our site for professional and enthusiast programmers.

1  
A quick search should yield plenty of solutions for decrypting your objects. You won't be able to copy them over as is, but you can certainly extract the scripts for them and then re-create them on the new server that way. Oh, and are you really downgrading from 2008 to 2005? Why? – Aaron Bertrand Aug 4 '12 at 16:14

I ran into this problem a few years back myself. However because SQL Server doesn't actually "encrypt" the objects that you create (it obfuscates them) you can quite easily reverse the process of obfuscation to get the definition back.I use the following procedure to script out encrypted objects:

CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
BEGIN

DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

    BEGIN TRANSACTION
        EXECUTE (@b)
        SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1) 
    ROLLBACK TRANSACTION

SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
SET @i=1
WHILE @i<=(DATALENGTH(@a)/2)
BEGIN
SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
SET @i=@i+1
END

SELECT @d [StoredProcedure]

END
ELSE
BEGIN
    PRINT 'Use a DAC Connection'
END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The original article where this can be found is http://social.msdn.microsoft.com/Forums/en/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330

I hope this is of help to you.

share|improve this answer
    
I couldn't get this one to work on SQL 2008 or SQL 2012 (@d is always NULL), but the other one posted by the same person, in that same thread, and also proposed as an answer, did work on both versions. – Aaron Bertrand Aug 4 '12 at 18:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.