There’s a group of customers that aren’t meant to have direct learn entry to the database tables. There are some predefined database views that the customers ought to have the ability to see the info by these views. In our case, the customers shouldn’t have the ability to even see the tables in SSMS or by any purposes that may connect with the database. Moreover, the customers ought to be as restricted as doable. For example, they shouldn’t even know what the supply desk names are. So SYS or INFORMATION_SCHEMA shouldn’t present any further info.

The very best strategy to obtain the targets is that we create a brand new database function and outline the customers as members of the brand new database function. We create a database function very simply although SSMS, however, if we’ve a number of views and we need to outline accesses by the UI it might be a time consuming course of. As well as, it will increase the chance of human faults throughout organising the configuration.

A quite simple method is to make use of the next T-SQL script that can create a database function, it’s going to additionally add the views because the function’s securables and it’ll grant the ample entry rights in order that any customers which might be members of the function have the ability to see the views. They’ll be additionally capable of execute the views and see the outcomes. You simply must ensure that the customers usually are not members of another roles which have overlap with the brand new function’s permissions.

Right here you go:

use [YOUR_DB]

create function [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 desk (no int, vu varchar(50))

declare @counter int

declare @vn varchar(50)

insert into @vu

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

set @counter = (choose depend(*) from @vu)

whereas @counter>=1

start

set @vn=(choose  vu from @vu the place no=@counter)

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

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

set @counter=@counter–1

finish

After executing the above code a brand new database function is created and now you simply want so as to add the consumer(s) as members of the function. You can do that in the course of the code as effectively, however, it’s worthwhile to add a line to the above code for every consumer which doesn’t appear to be simpler than utilizing the SSMS UI. To do  by SSMS:

1.       Develop the database

2.       Develop safety

3.       Develop roles

4.       Develop database roles

5.       Discover db_views and double click on on it

6.       Click on Add and add the consumer(s)

If you wish to verify if the above code actually added all views simply click on on “Securables” from the left pane.