Create Encrypted View In SQL Server 2008

Introduction

In this article I will demonstrate how to create encrypted view. Any user can see the definition of existing view by using sp_helptext or in the syscomments system catalog view. To avoid this we can use ENCRYPTION option in the CREATE VIEW which encrypts the view and you can no longer view the definition in syscomments system catalog view.

Encrypting a View

This example explains how to create Encrypted View.

Create vendors table

use person

create table vendors

(

vendorid int,

vendorname varchar(15),

vendorcity varchar(15),

vendorstate varchar(15)

)

Insert some records in table and select table.

insert into vendors values (20,'vipendra','noida','up')

insert into vendors values (21,'deepak','lucknow','up')

insert into vendors values (22,'rahul','kanpur','up')

insert into vendors values (23,'malay','delhi','delhi')

insert into vendors values (24,'mayank','noida','up')

select * from vendors

Vendors Table

Create a View

create view vendordata

as

select * from vendors where vendorid>22

View Definition

You can see the definition of view because it is created without using ENCRYPTION option.

Encrypted View

To create encrypted view, run following code:

create view Encrypted_vendordata

WITH ENCRYPTION

as

select * from vendors where vendorid>22

Now view the definition of Encrypted_vendordata

sp_helptext Encrypted_vendordata

Output:

How it works:

Using WITH ENCRYPTION option, a new view was created. It is a simulated security feature. When we create views or stored procedure without using WITH ENCRYPTION option, the code is saved in SysComments system table from where it can easily be seen by any user. The view may contain some conditions which are confidential so WITH ENCRYPTION option encrypts the code in SysComments system table and prevents users from viewing the original code.