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
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