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
@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