Problem:

There is a group of users that are not intended to have direct read access to the database tables. There are some predefined database views that the users should be able to see the data through those views. In our case, the users shouldn’t be able to even see the tables in SSMS or through any applications that can connect to the database. Additionally, the users should be as restricted as possible. For instance, they shouldn’t even know what the source table names are. So SYS or INFORMATION_SCHEMA should not show any additional information.

Solution:

The best possible way to achieve the goals is that we create a new database role and define the users as members of the new database role. We create a database role very easily though SSMS, but, if we have lots of views and we want to define accesses through the UI it would be a time consuming process. In addition, it increases the risk of human faults during setting up the configuration.

A very simple way is to use the following T-SQL script that will create a database role, it will also add the views as the role’s securables and it will grant the sufficient access rights so that any users that are members of the role be able to see the views. They’ll be also able to execute the views and see the results. You just need to make sure that the users are not members of some other roles that have overlap with the new role’s permissions.

Here you go:

use [YOUR_DB]

create role [db_views] authorization [dbo]

deny VIEW DEFINITION ON SCHEMA :: information_schema TO [db_views]

deny VIEW DEFINITION ON SCHEMA :: sys TO [db_views]

declare @vu table (no int, vu varchar(50))

declare @counter int

declare @vn varchar(50)

insert into @vu

select row_number() over (order by table_name) no,  TABLE_NAME from INFORMATION_SCHEMA.VIEWS

set @counter = (select count(*) from @vu)

while @counter>=1

begin

set @vn=(select  vu from @vu where no=@counter)

exec (‘grant SELECT ON OBJECT::[dbo].[‘+@vn+‘] TO db_views;’)

exec (‘grant control ON OBJECT::[dbo].[‘+@vn+‘] TO db_views;’)

set @counter=@counter–1

end

After executing the above code a new database role is created and now you just need to add the user(s) as members of the role. You could do this during the code as well, but, you need to add a line to the above code for each user which doesn’t seem to be easier than using the SSMS UI. To do  through SSMS:

1.       Expand the database

2.       Expand security

3.       Expand roles

4.       Expand database roles

5.       Find db_views and double click on it

6.       Click Add and add the user(s)

If you want to check if the above code really added all views just click on “Securables” from the left pane.