Encrypting a store procedure
Just like a view, stored procedure Transact-SQL definitions can have their contents encrypted in the database, removing the ability to read the procedure’s definition. Software producers who use SQL Server in their back-end, often encrypt stored procedures in order to prevent tampering or reverseengineering from clients or competitors. If you use encryption, be sure to save the original definition, as it can’t be decoded later(legally and reliably, anyhow). It should also be encrypted only prior to a push to production. In order to encrypt the stored procedure, WITH ENCRYPTION is designated after the name of the new stored procedure, as this next example demonstrates. Recompiling: do not want execution plan for stored procedure inside memory of SQL Server.
CREATE PROCEDURE usp_EncryptedSP WITH ENCRYPTION|RECOMPILE AS select * fromc category GO --Once you’ve created WITH ENCRYPTION, you’ll be unable to view the procedure’s text definition: -- View the procedure's text EXEC sp_helptext usp_EncryptedSP This returns: The text for object 'usp_EncryptedSP' is encrypted.
Tags: Stored Procedure