Encrypt All Procedures At a Time

Question

Encrypt All Procedures At a Time

SqlSpider...

Ten Centuries

Points: 1254
More actions
March 13, 2014 at 12:49 am

#305078

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SqlSpider... Ten Centuries Points: 1254 More actions · Answer 2

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,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!