How to Create Encrypted Stored Procedure

This article demonstrates how to create an encrypted stored procedure in SQL Server.

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

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.