ID Number: Q67713
1.10 1.11 4.20
OS/2
Summary:
In SQL Server, it is not possible to encrypt the text of a stored
procedure so that someone else cannot do an sp_helptext() call to see
the text of the stored procedure. However, the text of the stored
procedure can be deleted.
Once a stored procedure has been created, SQL Server never needs to
look at that text again; it is only saved for your reference. The
following steps can be used to delete the text of a stored procedure:
1. Be sure the flag is set to allow updates to the system tables.
[Use the sp_configure() function or the Config menu in SAF.]
2. Use the database that the stored procedure was created in.
3. Execute the following query:
UPDATE syscomments
SET text = ""
WHERE id = ( SELECT id from sysobjects
WHERE name = "<procedure name>" )
4. Reset the flag to allow updates to system tables (if desired).
If the sp_helptext() function is now called on the stored procedure,
no text will be displayed.
Note: The above steps can also be used for deleting the text of
triggers.
Additional reference words: 1.10 1.11 4.20 procedure text sp_helptext