PROCEDURES ENCRYPTION

Question

PROCEDURES ENCRYPTION

boris_shvartsman

Mr or Mrs. 500

Points: 582
More actions
December 9, 2003 at 12:47 pm

#59512

How can one encrypt 500 procedures in one shot or in at aleast 10 or so? Otherwise, after manually doing that, one needs a phsyciatrist..
lalafafa
if one wants it.. one will justify it.

Viewing 12 posts - 1 through 11 (of 11 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

If your stored procedures are small enough (< 4000 characters), you could write a cursor to get the text of the stored procedure, change CREATE to ALTER, add WITH ENCRYPTION, and then execute. Probably easier to do with some sort of client script such as VBScript, Perl, etc., though.

K. Brian Kelley, GSEC

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

boris_shvartsman Mr or Mrs. 500 Points: 582 More actions · Answer 2

that is a problem... Some are small, some are medium and some are huge.. no luck here.

lalafafa

if one wants it.. one will justify it.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Then your best bet is a script or small console that does the work for you. The reason for the 4000 character limit within SQL Server is the limit for an nvarchar variable, the parameter for EXECUTE() or sp_executesql.

K. Brian Kelley, GSEC

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

hbkdba Ten Centuries Points: 1224 More actions · Answer 4

just make a script containing all of them (or probably partially)

Open this script in query analyzer, do "find and replace" to change the headers of the stored procedures creations.

Christopher DeMeyer SSC Enthusiast Points: 131 More actions · Answer 5

In our company, I have made it a rule to always include a comment line like (--ForEncryption--) in the proper place within each SP, UDF, or VIEW created. Then on deployment of the application (to other then development server) we run a small windows app that uses ADO to open each replacing the comment line with the "WITH ENCRYPTION" tag, writing it back out. (this win app is automaticlly called from the installation wizard)

Christopher DeMeyer

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 6

You can export the sp to a text file, running with a cursor an OSQL string that saves the output to a text file.

With this you will get as many files as stored procedures you have. Then you can copy all the files into a single one, and with a text editor, replace CREATE PROCEDURE with ALTER PROCEDURE , AND maybe AS with WITH ENCRYPTION AS.

boris_shvartsman Mr or Mrs. 500 Points: 582 More actions · Answer 7

Christofer, thank you. I think you are the winner of my question/answer competition. I will enforse this rule. It will be a hell lot easier to just "REPLACE ALL" in one script rather then to count lines in procs, that may change and encrease with time. Great. Thanks all for your help. --Boris.

lalafafa

if one wants it.. one will justify it.

boris_shvartsman Mr or Mrs. 500 Points: 582 More actions · Answer 8

Christofer, your app.. is it your home app or something I can buy of the market?

lalafafa

if one wants it.. one will justify it.

noeld SSC Guru Points: 96590 More actions · Answer 9

quote:
... , AND maybe AS with WITH ENCRYPTION AS.

Be careful with THAT replacement you may ended replacing statements like

(CASE WHEN ...) AS Col1

Select

From (...) AS AS C(x,y)

Cast ( .... AS (...)

Incorrectly.

I think cdemeyer's is the way to go!!!

* Noel

cmore SSCrazy Points: 2452 More actions · Answer 10

quote:
The reason for the 4000 character limit within SQL Server is the limit for an nvarchar variable, the parameter for EXECUTE() or sp_executesql.

Actually you can get around that limit using execute() by concatenating together more than one variable- one of the instances in which I have deviated from using sp_executesql. The installer app sounds like the right solution though.

jcool SSC Eights! Points: 863 More actions · Answer 11

I have a rule to allways create objects with encryption, but in development it is commented out: /*WITH ENCRYPTION*/ AS

At deployment time I just do a replace /*WITH ENCRYPTION*/ with WITH ENCRYPTION

There are some exceptions where I don't want objects encrypted easily, like triggers or views to another database/server. I use /**WITH ENCRYPTION**/ there and still have an option to encrypt for higher security.