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

Encrypt All Stored procedures at once Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 2:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
Comments posted to this topic are about the item Encrypt All Stored procedures at once
Post #608897
Posted Thursday, September 10, 2009 8:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 4:06 PM
Points: 104, Visits: 855
how to Dcrypt it...

Regards,
Shivrudra W
Post #785750
Posted Tuesday, December 15, 2009 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 17, 2010 10:16 PM
Points: 8, Visits: 39
worked well on some stored procs but missed some for no real reason that I can see.

Very handy non the less.

Thanks
Post #834390
Posted Monday, May 10, 2010 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 7, 2014 8:55 PM
Points: 19, Visits: 201
I think this code is prone to errors. If 'AS' is part of the parameter's name, for example.
Post #919101
Posted Tuesday, February 8, 2011 1:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:57 AM
Points: 55, Visits: 159
you can't decrypt it, but if you follow the instructions there should be a backup up of your sp's in the database...
Post #1060608
Posted Wednesday, July 20, 2011 8:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:49 PM
Points: 20, Visits: 164
There are a couple of issues I found with the script Chris.

1- If there was no BEGIN after the AS at the beginning of the SP then the WITH ENCRYPTION syntax could not be inserted into the text of the SP

2 - Because the way syscomments stores the text of the SP's (by spreading the text over multiple rows of the system view when the text of the SP is over a certain length), that when trying to join all the text back together, some text is truncated because of the insertion of more text when inserting 'WITH ENCRYPTION', this then breaks the SP therefore when trying to alter it, by running EXEC (@sptext) it fails and is therefore caught by the CATCH statement which says

the stored procedure 'SPNAME' cannot be encrypted automatically.


You will find any SP that is over 'apprx' 200 lines, that your script wont automatically encrypt them. Also if there is no BEGIN after the AS statement, that the script will also fail for that SP.

i.e.

ALTER Procedure [dbo].[BCPReplicationIn]
AS
begin <-- If this BEGIN doesn't exist the script wont insert WITH ENCRYPTION before the AS.

I was trying to get your script to work, but it seems like a hard task mainly because of the way sys.syscomments stores the text of the SP's. Hopefully you can find a way to make it work because it would be extremely handy to use.

Thanks,

Cameron


Post #1145570
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse