Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Encrypt All Procedures At a Time


Encrypt All Procedures At a Time

Author
Message
SqlSpider...
SqlSpider...
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38980
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...
SqlSpider...
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
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,
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47269 Visits: 44392
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38980
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search