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 the 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 for encrypting 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 with the help of a sp_helptext command.