Encryption of text in Stored Procedures, Triggers and Functions in MS SQL Server

There are still database programmers that are not aware that it is possible to encrypt sensitive codes in Microsoft SQL Server. Letting unauthorized or ordinary users to see what rules running your database such as stored procedures and triggers can be a serious threat to your data so encryption is of these codes is highly recommended.

Encryption of text in Stored Procedures, Triggers and Functions in MS SQL Server has been around from quite some time. To be exact its already available as early as SQL 2000. Although encryption in SQL 2000 is too lenient, it has been addressed in later versions. Encryption is easy, you’ll have to use the “WITH ENCRYPTION” option when creating triggers and stored procedures.

Let us check an Example,

For Store Procedure:

CREATE procedure [dbo].[EncryptedProc]
WITH ENCRYPTION AS
BEGIN
SECLECT Empid,EmpName from Employee
END

For Triggers:

CREATE TRIGGER MyEmployeeTable_Trigger ON Employee
WITH ENCRYPTION
AFTER INSERT
AS
PRINT ‘This is encrypted trigger’
GO

Advertisements

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