> Basically, I don't want any body to be able to view my existing
> procedures except me. Is this possible? If so, how do I do this?
You can encrypt them, but then no one can easily read them, including you.
First make a backup copy of the procedures and store them somewhere for your
use only (source code control is perfect for this), then use the WITH
ENCRYPTION key words to encrypt the procedures. For example:
CREATE PROCEDURE uspTest
AS
SELECT *
FROM Authors
GO
sp_helptext uspTest
GO
ALTER PROCEDURE uspTest
WITH ENCRYPTION
AS
SELECT *
FROM Authors
GO
sp_helptext uspTest
GO
The first execution of sp_helptext will show what's been stored in the
Syscomments system table, but the second execution of sp_helptext will show
this message: "The object's comments have been encrypted." Everyone who has
permissions to run the SP will be able to, but no one will be able to read
the source after the encryption unless he uses a hacker's tool.
If encryption is news to you, realize that it's probably not news to the
company's DBA and programmer. If they're experienced, they already know how
to decrypt the SP's. But a lot of companies don't employ experienced SQL
Server people, so your encrypted SP's may be safe from prying eyes for the
time being.
Alternatives are extended stored procedures and, if you have SQL Server 2005,
writing procedures with a CLR language. These require skills beyond T-SQL,
but the code is separate from SQL Server and requires other, more
sophisticated hacker tools to read the source code.
Jay - 27 Nov 2006 09:15 GMT
Ok, sounds perfect for me.Someone can delete/overwrite it but that is
ok with me as no one can "easily" read my logic.
Thanks.
> > Basically, I don't want any body to be able to view my existing
> > procedures except me. Is this possible? If so, how do I do this?
[quoted text clipped - 43 lines]
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-security/200611/1
Arnie Rowland - 27 Nov 2006 17:57 GMT
Jay,
As Chris indicated, 'easily' is extremely relative. Anyone that is a little
bit SQL Server 'savvy' and knows how to use Google will readily 'break' the
encryption.
It's like the lock on the front door of your home, it only keep the honest
people out.

Signature
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
> Ok, sounds perfect for me.Someone can delete/overwrite it but that is
> ok with me as no one can "easily" read my logic.
[quoted text clipped - 57 lines]
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-security/200611/1
Chris O'C - 28 Nov 2006 05:03 GMT
> Ok, sounds perfect for me.Someone can delete/overwrite it but that is
> ok with me as no one can "easily" read my logic.
>
> Thanks.
You're welcome, but before you think it's "perfect," do an Internet search to
find out how easy it might be to find out how to decrypt the stored
procedures.