Re: Securing my Stored Procedures?

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.



Chris O'C via SQLMonster.com wrote:
Jay wrote:
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.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-security/200611/1

.

Relevant Pages

  • Re: major security concern - any sql user with minimal permission can see code for all stored procs
    ... i have hundreds of SPs and views, and just for adding 1 user, i dont want to ... > procedures and triggers using the with encryption option. ... > better approach to protecting your source code in your type ... >>connected from a remote location to my SQL server at port 1433, ...
    (microsoft.public.sqlserver.security)
  • Re: Securing my Stored Procedures?
    ... As Chris indicated, 'easily' is extremely relative. ... ENCRYPTION key words to encrypt the procedures. ... CREATE PROCEDURE uspTest ... Alternatives are extended stored procedures and, if you have SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Securing my Stored Procedures?
    ... ENCRYPTION key words to encrypt the procedures. ... CREATE PROCEDURE uspTest ... Alternatives are extended stored procedures and, if you have SQL Server 2005, ... sophisticated hacker tools to read the source code. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL or Access DB
    ... As far as encryption goes though... ... with Sql Server you can use SQL DMO and encrypt your stored procedures ... installation - Security was absolutely critical and in most instances, ... > then we create a nice gui around this database and sell it to automotive ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Encrypted software backups?
    ... The more you spread it around, ... I'd encrypt the source code before distributing copies of it to ... Although good encryption would minimize the ... copies among friends and relatives. ...
    (sci.crypt)