Encrypting Stored Procedure Code

Hi,
Is it possible to encrypt the code within a stored procedure in
Microsoft SQL Server?
My example is:
I've written a stored procedure. I don't want anyone to be able to
view the contents/code within this stored procedure unless I allow them
to see what is in it.
Thanks,
Darrin> Is it possible to encrypt the code within a stored procedure in
> Microsoft SQL Server?
Sure, but it is not very secure. A google search will yield plenty of
decryption algorithms. For example:
http://searchsqlserver.techtarget.c...1056869,00.html|||Once you encrypt a stored procedure you can not decrypt it (within SQL
Server)
So you can't show it to people who you want to show it to
example
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
But like Aaron said this is very weak, there are some vb apps out there
that will decrypt this
http://sqlservercode.blogspot.com/|||I know there are decryptors out there for SQL2000 but do you know of nay tha
t
have been written for SQL2005?
We encrypt our procs mainly as a safeguard against accidentally altering
them but do need a decryptor when we need to edit them. Our upgrade to
SQL2005 will be delayed untill we have a decryptor.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sure, but it is not very secure. A google search will yield plenty of
> decryption algorithms. For example:
> [url]http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html[/url
]
>
>|||>I know there are decryptors out there for SQL2000 but do you know of nay
>that
> have been written for SQL2005?
I will admit that I have only spent 5 minutes looking, but I have yet to
find where the encrypted text is stored, since sys.sql_modules.definition,
sys.syscomments.ctext and object_definition() all return NULL. My guess is,
to make the encoding a little more obscure, that they stuff this into
mssqlsystemresource, or hide it in some obscure system view. So, you may be
able to get to it, you may not.

> We encrypt our procs mainly as a safeguard against accidentally altering
> them but do need a decryptor when we need to edit them.
Isn't that what source control is for? And doesn't that defeat the purpose
of encrypting them in the first place? If someone can accidentally alter a
production procedure, they can also do accidentally after using a decryption
method to view the text. Encryption does not, and will never, solve the
problem of lack of adherence to proper process. My suggestion is to correct
the process.
A|||Thanks for the response. Yes, I fully appreciate the importance of source
control and am confident that our dev department use it properly.
As a production DBA, though, looking after 100+ SQL Servers, when called at
2am to fix a performance "issue", the Decryptor is essential as you dont hav
e
the time to delve into VSS. Also its essential when you need to compare an
existing proc to a proc in Source Safe.
With the move to SQL2005, we will, as suggested, need to look at our
process. It may mean moving away from encrypting procs.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> I will admit that I have only spent 5 minutes looking, but I have yet to
> find where the encrypted text is stored, since sys.sql_modules.definition,
> sys.syscomments.ctext and object_definition() all return NULL. My guess i
s,
> to make the encoding a little more obscure, that they stuff this into
> mssqlsystemresource, or hide it in some obscure system view. So, you may
be
> able to get to it, you may not.
>
> Isn't that what source control is for? And doesn't that defeat the purpos
e
> of encrypting them in the first place? If someone can accidentally alter
a
> production procedure, they can also do accidentally after using a decrypti
on
> method to view the text. Encryption does not, and will never, solve the
> problem of lack of adherence to proper process. My suggestion is to corre
ct
> the process.
> A
>
>