Если вы про стандартные возможности, то
1.
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.

Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.

2.
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]

WITH ENCRYPTION
Encrypts the syscomments entries that contain the text of CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.


! НО !
Уже есть утилиты, которые умеют читать созданные таким образом процедуры и триггеры
см. http://www.sql.ru/cgi-bin/UltraBoard/UltraBoard.pl?Action=ShowPost&Board=mssql&Post=2627&Idle=365&Sort=0&Order=Descend&Page=6&Session=