The best way to handle the person entry rights to see database views however not supply tables utilizing T-SQL

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 information via these views. In our case, the customers shouldn’t have the ability to even see the tables in SSMS or via any functions that may hook up with the database. Moreover, the customers must 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 extra data.

The very best approach to obtain the targets is that we create a brand new database position and outline the customers as members of the brand new database position. We create a database position very simply although SSMS, however, if we now have a number of views and we need to outline accesses via the UI it could be a time consuming course of. As well as, it will increase the chance of human faults throughout establishing the configuration.

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

Right here you go:

use [YOUR_DB]

create position [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 position is created and now you simply want so as to add the person(s) as members of the position. You could possibly do that through the code as nicely, however, that you must add a line to the above code for every person which doesn’t appear to be simpler than utilizing the SSMS UI. To do via 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 person(s)