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
easiest way is to simply connect via the DAC: Dedicated Admin connection.
procs are not encrypted when connecting via the DAC.
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;
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
exec('Select * from ' + @TblName)
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.
Viewing 3 posts - 1 through 2 (of 2 total)