How to Create an Encrypted Stored Procedure

Introduction

This article demonstrates how to create an encrypted stored procedure in SQL Server. This article starts with introduction of how to create a stored procedure in SQL server.  After that, it demonstrates how to encrypt stored procedure.

Every developer wants to secure her/his SQL code. For this, we will use encryption. Encryption is a good but not utterly tenable process. In this post, 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:   non-encrypted Stored Procedure

-- =============================================

CREATE PROCEDURE spGetTestData

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

**

Figure 1

Encrypt Stored Procedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Gaurav Malviya

-- Create date: 12-01-2014

-- Description:   Encrypted Stored Procedure

-- =============================================

CREATE PROCEDURE SpGetTestDataEncrypt

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

**

Figure 2

* *

Summary

In this article, I discussed how we can encrypt stored procedure in SQL Server. After that, we saw that we can not get code by the help of sp_helptext command.