Stored Procedure Encryption in SQL Server

Because of some security policies we need to require our code to be safe from Users who are going to use SQL Server database and objects of them and some outside threat. We have different different users to access the database objects or used for application.

We have so many ways to encrypt data, but here i am talkig about the code encryption. With this encryption security, Users can execute the stored procedures but can not view the code.

Let us check the how the Stored Procedures can be encrypted.

–Create Database Demo

Create Database Demo

go

— Creating table

IF (OBJECT_ID(‘UserMaster’,‘U’)> 0)

DROP TABLE UserMaster

GO

CREATE TABLE UserMaster

( UserId INT,

UserName VARCHAR(100),

UserPwd NVARCHAR(100) )

GO

— Inserting demo records

INSERT INTO UserMaster

SELECT ‘1’,‘User1’,‘pwd1’

UNION ALL

SELECT ‘2’,‘User2’,‘pwd2’

UNION ALL

SELECT ‘3’,‘User3’,‘pwd3’

GO

———————————————-

— Creating Stored Procedure without encryption

CREATE PROCEDURE GetUserDataWithoutEncrypt

AS

BEGIN

SET NOCOUNTON

SELECT UserID , UserName , UserPwd FROM UserMaster

END

GO

— Creating Stored Procedure with encryption

CREATE PROCEDURE GetUserDataWithEncrypt

WITH ENCRYPTION

AS

BEGIN

SET NOCOUNTON

SELECT UserID , UserName , UserPwd FROM UserMaster

END

GO

————————————–

EXEC GetUserDataWithoutEncrypt

EXEC GetUserDataWithEncrypt

GO

——————————————-

EXEC SP_HELPTEXTGetUserDataWithoutEncrypt

EXEC SP_HELPTEXTGetUserDataWithEncrypt