Microsoft KB Archive/67713

INF: Deleting Text from SQL Stored Procedures and Triggers 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:

  Be sure the flag is set to allow updates to the system tables. [Use the sp_configure function or the Config menu in SAF.]   Use the database that the stored procedure was created in.   Execute the following query: UPDATE syscomments SET text = &quot;&quot; WHERE id = ( SELECT id from sysobjects               WHERE name = &quot; &quot; )   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