Encryption of View in SQL Server

Introduction

Today we are going to learn how to encrypt the logic of a view from the end user or others. There are several reasons to hide the logic of a view. The most important reason is security. We encrypt the view using the "with encryption" keyword.

I assume you have a basic understanding of views. For more help visit, View in SQL Server. In my previous article I described how to encrypt a Stored Procedure; for that you can visit Encryption of Stored Procedure in SQL Server.

First of all we create a table named student. After that we create a view named view1 without encryption. Then we show the logic of that view with the help of a command. After that we create another encrypted view named view2. And show that we can't see the logic of the encrypted view.

Creation of table:

create table emp(empId int, empName varchar(15), empAdd varchar(15))

Insertion of data:

insert into emp

select 1,'d','canada'union all

select 2,'e','la'union all

select 3,'f','usa'

Output:

Creation of unencrypted View:

create view view1

select * from emp

Executing this View:

SELECT * from view1

Output:

Logic of this View:

exec sp_helptext view1

Output:

Creation of encrypted View:

create view view2

with encryption

select * from emp

Executing of this View:

select * from view2

Output:

We can't see the logic of the encrypted View.

Output:

If we try to see the logic of the encrypted message it shows the following message:

exec sp_helptext view2

Summary

In this article I described encryption of views in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.