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.