|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:04 AM
Points: 269,
Visits: 1,110
|
|
Hi,
I am a DBA, I have tried to decrypt my encrypted SP, with your DECRYPTSP2K in 2008 SQL server, but that dropped my inputed procedures, is there is any way for 2008
Many Thanks! S.saravanan “I am a slow walker, but I never walk backwards- Abraham Lincoln”
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613,
Visits: 27,669
|
|
easiest way is to simply connect via the DAC: Dedicated Admin connection. procs are not encrypted when connecting via the DAC.
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 11,613,
Visits: 27,669
|
|
ok a correction. it's more than just the DAC, as i just double checked to make sure my post was correct.
you need to connect via DAC, and then use the stored procedure from this web site; http://msdynamicstips.com/2008/12/24/decrypt-sql-2005-stored-procedures-functions-views-and-triggers/
with THAT, you can read the proc contents.
here's my step by step if you care to recreate it. first, i created an encrypted procedure:
--for users who are too lazy to type "SELECT * FROM" CREATE procedure sp_show --USAGE: sp_show gmact @TblName varchar(128) WITH ENCRYPTION As Begin exec('Select * from ' + @TblName) End GO EXEC sp_helptext sp_show --returns "The text for object 'sp_show' is encrypted."
next, i connected to the DAC via SSMS: you might need to enable the DAC by going to facets>>Surface Area Configuration toggle "RemoteDACEnabled" to true, then stop and start the service (you are doing this on TESt, no production, right!?) next i connected via the DAC:

next, in that DAC window, i created the stored procedure [dbo].[SQLDecryptor]
finally, i simply ran the command
EXEC [dbo].[SQLDecryptor] 'dbo','sp_show'
and it printed the definition in the Meassages field of the SSMS window.
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|