Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DECRYPTSP2K Issues in 2008 Expand / Collapse
Author
Message
Posted Thursday, January 19, 2012 6:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 6:14 AM
Points: 280, Visits: 1,207
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”
Post #1238608
Posted Thursday, January 19, 2012 6:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 12,910, Visits: 32,021
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
Post #1238611
Posted Thursday, January 19, 2012 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 12,910, Visits: 32,021
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
Post #1238653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse