RSS

Stored Procedure Encryption in SQL Server

30 Aug

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

 
Leave a comment

Posted by on August 30, 2011 in Encryption Tips

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s