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 Procedures At a Time Expand / Collapse
Author
Message
Posted Thursday, March 13, 2014 12:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:34 AM
Points: 88, Visits: 286
Hi All,

I need to prepare procedure text by using with encryption. For Now I am doing manually, but i need to do it all procedures at a time by using script.

Can anybody please let me know how can i achieve this by using script.

Thanks in advance.

Post #1550568
Posted Thursday, March 13, 2014 4:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 12,910, Visits: 32,013
Basically its a find and replace operation... you can select definition from sys.sql_modules to get the CREATE PROC statements but you need to change it to alter and add with encryption in the right.place.

Probably easier in a real text editor so id script outnall thenprocsnand do that in editplus or notepad++


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 #1550605
Posted Thursday, March 13, 2014 4:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:34 AM
Points: 88, Visits: 286
Lowell (3/13/2014)
Basically its a find and replace operation... you can select definition from sys.sql_modules to get the CREATE PROC statements but you need to change it to alter and add with encryption in the right.place.

Probably easier in a real text editor so id script outnall thenprocsnand do that in editplus or notepad++


Thanks Lowell,

I have to do that in all the times, means i have to prepare more that 2 times in a month, for that is there any one time script, so i can execute that procedure in the selected database and i will get all the encrypted procedures.

Thanks,
Post #1550612
Posted Thursday, March 13, 2014 5:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
Lowell's told you how to do it. If you want a script run once off to encrypt all procedures, write one up that does what he said.

Why are you going this route anyway? You do know just how easy that 'encryption' is to reverse?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1550620
Posted Thursday, March 13, 2014 7:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 12,910, Visits: 32,013
find and replace, in a proper text editor, because you would visually confirm each change, is absolutely the way to go.

a blanket find and replace is not going to be 100% accurate due to coding styles and stuff;
for example: if you search for CREATE PROCEDURE to repalce it with ALTER PROCEDURE
any differences in whitespace betweent he two words, or where a develoepr wrote CREATE PROC instead;

WITH ENCRYPTION needs to right before the AS keyword, but it depends on coding style, whether you can find 'nAS, or whitespace around it

-- Create Encrypted SP
CREATE PROCEDURE uspEnc
WITH ENCRYPTION
AS
SELECT GETDATE()




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 #1550677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse