I want to hide the script of a stored procedure in SQL Server 2008

Question

I have written a stored procedure in SQL Server.

Now its just that I don't want any one to see my script or edit it.

Please remember that I am working on the a standard login id and password. Which is shared with every one.

Hence some way where I can allow every one to execute the procedure. But they shouldn't see the script.

Cheers! thanks

Answer 1

You're looking for WITH ENCRYPTION, which encrypts the code behind your stored proc.

CREATE PROCEDURE usp_MyProc
WITH ENCRYPTION
AS
SELECT *
FROM myTable

Just a caveat, from MSDN:

Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users who can either access system tables over the DAC port or directly access database files.

Some references and further reading:

• http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/
• http://msdn.microsoft.com/en-us/library/ms187926.aspx

Answer 2

SQL Server doesn't truly provide a foolproof method to protect module code. The WITH ENCRYPTION clause should be named something along the lines of WITH LOOSE_OBFUSCATION, since the "encryption" is very easily thwarted. You're not going to be able to use anything in SQL Server to make the code undecipherable by anyone except the most casual onlookers - anyone determined is going to be able to beat native methods without breaking a sweat.

While this may be good enough for your needs, probably a safer way (but still not perfect) is to put some or all of the procedure's business logic into the CLR (read more here).