Re: Any script to encrypt the all SPs and Views in one go
From: Rayan Yellina (ryellina_at_hotmail.com)
Date: 06/15/04
- Next message: Prasad Koukuntla: "Re: how to refresh objects if a column changes"
- Previous message: Hugo Kornelis: "Re: oSQL - How do I run all .SQL files in a folder by running one batch"
- In reply to: Aaron [SQL Server MVP]: "Re: Any script to encrypt the all SPs and Views in one go"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Jun 2004 15:04:39 -0700
Aaron,
Thank you very much for your complete and detail
explanation. Before I saw your answer I already decided to
generate script for all Views & Stored Procs and
added "WITH ENCRYPTION" manually. It took me couple of
hours, it was ok for me. So now even you advise it too...
>From my experrience, I wanted to tell everyone that always
put "WITH ENCRYPTION" in comments (--WITH ENCRYPTION)when
you create any SP or View, so that in future they can just
go to Edit->Find&Replace option
--WITH ENCRYPTION
to
WITH ENCRYPTION
So, every thing will change in a second.
I hope this will help everyone. Thank you.
-Rayan.
>-----Original Message-----
>I don't think there is a simple way to do this. You
could try to do it in a
>T-SQL cursor (e.g. SELECT ROUTINE_DEFINITION FROM
>INFORMATION_SCHEMA.ROUTINES), but you will be faced with
many obstacles:
>
>(a) you won't easily be able to tell where to add
the "WITH ENCRYPTION"
>text. Since some procedures might start with AS / BEGIN,
have varying
>layouts for parameters, etc. You can't just
replace "dbo.procedurename"
>with "dbo.procedurename with encryption" because it might
be mentioned in
>other places in the code.
>
>(b) if the procedure or view is > 8000 characters, you
won't be able to
>automate re-creation, because it will come back as
multiple rows.
>
>(c) automated script reproduction will be further
troubled by stored
>procedures that use EXEC() or other things that require
nested quotes,
>nested double-quotes, etc. It will take a lot of REPLACE
() work and trial
>and error to produce another script which can be run in
an automated
>fashion.
>
>(d) I'm sure there are others I'm not thinking of.
>
>What you should consider doing is generating a
drop/create script for all
>procedures and all views, manually edit them to say WITH
ENCRYPTION, and
>send them that script.
>
>Also, be aware that a 2-second google search will yield a
script that will
>decrypt these objects, so the current implementation for
encrypting objects
>within SQL Server will only stop the casual observer.
>
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>
>
>"Rayan Yellina" <ryellina@hotmail.com> wrote in message
>news:1cd7401c45300$f456c850$a401280a@phx.gbl...
>> Hi,
>> Is there any script to encrypt all the stored procs
and
>> views from the database, instead of Altering them
>> with "WITH ENCRYPTION" manually for each and every
Stored
>> proc and view.
>> The situation is, until now we never encrypted any views
>> or stored procs for our clients, but from now onwards we
>> have to encrypt them. What is the fastest or proper way
to
>> do it. We do not have access to our client databases, so
>> we have to mail a CD with our scripts and they can run
it.
>> That is why I am asking that whether there is an
>> usual/common way of doing this. Any work-arounds or your
>> experiences of doing this. Thank you very much.
>> -Rayan
>
>
>.
>
- Next message: Prasad Koukuntla: "Re: how to refresh objects if a column changes"
- Previous message: Hugo Kornelis: "Re: oSQL - How do I run all .SQL files in a folder by running one batch"
- In reply to: Aaron [SQL Server MVP]: "Re: Any script to encrypt the all SPs and Views in one go"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
