Views in SQL Server with Example-Part1

Hello friends, today I am going to talk about one of my favorite topic “Views” in SQL Server. This topic is one of my favorite topics. Whenever I ask somebody why we need Views in SQL Server, I never get a satisfactory answer. Hence I thought to share my thoughts about this topic. Let’s see the definition of View first then we learn why we need it.

What is a View: - A View in SQL Server can be defined as a logical/virtual table structure whose content (rows and columns) is defined by a select statement of the View.

Need of View: - It is important to understand the need of Views, below are the points which define why a View is important.

Security Mechanism: - The most important fact of existence of a View is, its Security Mechanism. To understand the security mechanism of a View, let’s take an example. I have client’s financial database with me and I do not want to reveal all the sensitive information of their employees (in Employee table) to all of the developers in my development team. What would I do?

Solution :-  I will create a View on Employee table  with only limited set of columns(which are not confidential) of "Employee" table  and create a new login and grant permission to access only the View not the "Employee" table. To understand this lets take an example.

I have created "Employee" table using below SQL statement and inserted few rows into it.

Create table Employee(Eid int,Name varchar(50),Basic_Sal int,HRA int)

Insert into Employee values(101,'Neeraj',50000,10000)

Insert into Employee values(102,'Ram',30000,5000)

Insert into Employee values(103,'Rachit',24000,4900)

Insert into Employee values(104,'Akshay',49000,23000)

Insert into Employee values(105,'Ankit',38000,4300)

Let’s create a View using Below SQL Statement (Syntax of Creating a Simple View):-

Create view vw_Employeeà [Create View followed by View Name]

as

select Eid,Name from Employee à[Statement defining the View]

Let’s create a Login name “User” by following below steps:-

We will login to SQL Server using “Sa” credential or “Windows Authentication” as these two credentials are having full access to the Server. Then right click on the login folder and click on “New Login” menu as shown in the below screenshot.

Once we click on the “New Login” Menu, a New screen will appear as shown in the below screenshot.

In the above screenshot, there are five circles. In first circle, I have put the name of the Login “User”.In the second circle, I have selected the “SQL Server authentication” mode. In third circle, I have set the password and confirmed the password. In fourth circle, I have unchecked the “Enforced password policy”. 

Click on the Menu “User Mapping” (fifth circle) that will bring a new Screen as shown in the below screenshot.

Check all the "checkboxes" (encircled by blue color) to give access of the all databases and then check the "checkbox" db_owner encircled by red color.Click on the “OK” button which is at the bottom right corner.Since we have made the Login (User) as “Db_owner”, we will be able to access any object and can do any operation (Select,Insert,Update,Delete) on any object. Now we will revoke all the rights from the "Employee" table for login(User) by following below steps.

Right click on “Employee” tableàProperties as shown in the below screenshot.

Once we click on “properties” Menu, this action will bring a new screen to us as shown in the below screenshot.

Click on the “permissions” Menu, which will bring a new look to the current screen as shown in the below screenshot.

Click on “Search” button(encircled by red color), which will bring a new screen as shown below.

We will put the login name “User” in the text field and click on “Check names” button to verify the login name entered in the text field is correct and then press “OK” button. Once we click on the “OK” button, the current screen will be closed and we will be on the previous screen.

In the permission section for the user, we can choose which permission we want to give the user and which we do not. For convenience purpose, I have checked all the checkboxes of “Deny” column, to revoke all the permissions from the Login/User on “Employee” table. Finally click “OK”. Now lets check if the permission is revoked for table “Employee”. To check this, we need to login to the SQL using “User” login.

In the above screenshot, we can see that, I am logged in to the SQL Server using “User” login and I have run the select query on table “Employee” which is throwing an error message stating that “The select permission was denied on the object Employee, Database Demo, schema, dbo”.

Since the Login(User) is a db owner, It can access all the objects, except “Employee” table as we have explicitly removed all the rights from “Employee” table. Lets try to access the View.

Even though we do not have access to the base table “Employee”, we are able to access the data of the employee table using View.This is the most important feature of View which is ignored by most of us.

Conclusion: - Views are used to apply row level or column level security on the base tables. The example shown above was related to column level security. Similarly we can apply row level security by selecting the specific no of rows of table in a View.

I will explain rest of features of View in my next article. Please feel free to ask any question related to this topic and share your feedback on this topic.