How to Create Encrypted Stored Procedure

Introduction

This article demonstrates how to create an encrypted Stored Procedure in SQL Server. This article starts with an introduction to the creation of a Stored Procedure in SQL Server. Then, it demonstrates how to encrypt a Stored Procedure.

Every developer wants security of her/his SQL code like a Stored Procedure. For this, we will go for encryption. Encryption is a good but not utterly tenable process. In this article, I would like to show you some best practices to encrypt SQL Server code.

Creating a normal Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:                      Gaurav Malviya
-- Create date: 12-01-2014
-- Description:   un-Encrypt Stored Procedure
-- =============================================
CREATE PROCEDURE sp_gettestdata
           
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      SELECT * FROM test_table
END
GO

For Check Stored Procedure



Encrypt Stored Procedure

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:                      Gaurav Malviya
-- Create date: 12-01-2014
-- Description:   Encrypt Stored Procedure
-- =============================================

CREATE PROCEDURE sp_gettestdataEncrypt

WITH ENCRYPTION
AS
BEGIN
         -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
      SET NOCOUNT ON;
      SELECT * FROM test_table
END
GO

Again Check Stored Procedure



Summary

In this article, I showed how to encrypt a Stored Procedure in SQL Server. We then saw how to set an image to get code for the help of a sp_helptext command.