May 28, 2010 at 9:03 am
I have a set of 7 databases of identical structure (Different clients using the same system). I need to encrypt the objects in the database as a way of protecting my IP. Is there a way I can encrypt objects without having to do so view after view, stored procedure after stored procedure etc?
May 28, 2010 at 9:42 am
What I would do is get the scripts of all your objects. I assume you have these in source control, but you could generate a script using SSMS that has all the store procedures in it.
I always have the "as" on a separate line, so I can do a search replace of "as" with "with encryption as", then run the script.
Be sure you use ALTERs for your stored procedures.
May 28, 2010 at 9:44 am
There's no bulk-encrypt option, objects have to be encrypted with an ALTER statement. Maybe a tool like Redgate's SQL Refactor could do it for you, or you could write something to get the definition of each object and add the WITH ENCRYPTION clause
Do note that encryption is pretty easy to undo. You're not protecting anything, at best it's a speed bump, nothing more.
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
May 28, 2010 at 9:53 am
Thanks Steve. It has worked like a german car! Nice technique.
May 28, 2010 at 9:56 am
Just be careful you aren't using any alias like
select as.*
from AppleSales [as]
May 28, 2010 at 9:59 am
I'll search the scripts occurrence by occurrence just to be sure. Thanks
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply