How to grant permissions only to view in sql server

There are times you want to grant access only to views for a certain users or groups. If you just have only few views in your database it might be okay to grant access on each one. However if we talk about hundreds of view it will be very tedious task to grant access on each view. To make our live much easier we could use the INFORMATION_SCHEMA.VIEWS and a loop to directly give our users access or create a role that has only access to a view and grant our users that role. In this blog post we will see how to grant permissions only to view in SQL Server.
I selected to discuss two methods that might provide easier means to do that. The first method will be granting permission to read on all views directly to the user or user group. The second method will be creating a database role and give permission only to read on views and we may assign the role to any number of users or groups we might have.
Let us see both methods in action.
Let us first prepare our environment for our experiment. I am going to create a database, a table and a view.

create database db1;
go
 
use db1;
go
 
create table table1(
	id int not null identity,
	column1 varchar(20),
	column2 varchar(20)
);
go
 
create view view1
as 
	select id,
		   column1,
		   column2
	from table1;
go

Now that we have our environment let us give permission to our Active Directory group “Testers” that has one user “Tester1”.

--Method 1--
--Grant access directly to [DBANDBI\Testers]--
DECLARE @SQL NVARCHAR(3000);
DECLARE @loginName NVARCHAR(100) = 'DBANDBI\Testers'; /*These are the user group we want to give permissions to*/
 
DECLARE GrantExec_Cursor CURSOR FAST_FORWARD READ_ONLY
FOR
	SELECT 'GRANT SELECT ON [' + TABLE_SCHEMA  + '].[' + 
		TABLE_NAME  + '] TO [' + @loginName + ']; ' AS SQLstatement
	FROM INFORMATION_SCHEMA.VIEWS;
 
OPEN GrantExec_Cursor
FETCH NEXT FROM GrantExec_Cursor INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN 
    EXEC(@SQL)
    FETCH NEXT FROM GrantExec_Cursor INTO @SQL
END 
GO
 
CLOSE GrantExec_Cursor
DEALLOCATE GrantExec_Cursor
GO

Now if we right click on the view and select properties surely we manager to grant SELECT permission to Testers group.

But let us check if we only have given we access on the view. To do that let us open SSMS as Tester1 ( member of Tersters group) as

We will get the following error

Or if we try to change a user from one of opened query windows, that will also result in error.

execute as user='dbandbi\tester1'
go

We have given the user group a permission to read from the view but we still have to give them permission to connection to the database and we could do that as follows.

USE db1;
GO
GRANT CONNECT TO [DBANDBI\Testers];
Go

After connecting to the database we can be able to select from all views but not from any of the tables.


On the second method we will create a database role (DBandBI_Testers_Role) and we will make Testers group a member of this group.

--Method 2--
 
/*Create role*/
CREATE ROLE DBandBI_Testers_Role;
go
 
/*Grant select on all views in dbo*/
DECLARE @SQL NVARCHAR(3000)
 
DECLARE @RoleName NVARCHAR(100) = 'DBandBI_Testers_Role';
 
DECLARE GrantExec_Cursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT 'GRANT SELECT ON [' + TABLE_SCHEMA  + '].[' + 
                             TABLE_NAME  + '] TO [' + @RoleName + ']; ' AS SQLstatement
FROM INFORMATION_SCHEMA.VIEWS
 
OPEN GrantExec_Cursor
FETCH NEXT FROM GrantExec_Cursor INTO @SQL
 
WHILE @@FETCH_STATUS = 0
BEGIN --Grant Permissions
    EXEC(@SQL)
    FETCH NEXT FROM GrantExec_Cursor INTO @SQL
END --Grant Permissions
 
CLOSE GrantExec_Cursor
DEALLOCATE GrantExec_Cursor
GO

Then we make users be a member of that role

EXEC sp_addrolemember 'DBandBI_Testers_Role', 'DBANDBI\Testers';
Go

/*Then we need to let our users be able to connect to the database using*/
GRANT CONNECT TO [DBANDBI\Testers];
Go