Our client requirement, is to restrict other users from viewing/accessing the database if they are not assigned to work on it.
Some background on how the databases are setup:
For each client, a new database is created to host their data. Also there are other databases which are used by the front end application.
There are three types of users:
- Data manager - modify tables according to data, load data, execute SP
- Application user - used by front end web application
- SME - They are data experts who work on the DB in need
All of the above mentioned users are configured as SysAdmin role. We understand this is not a good practice and efforts are in place to assign appropriate role to each db user
Consider this scenario:
- 5 databases
- 3 data managers and 3 SME's and 1 application user.
1 data manager and 1 SME are assigned to work on the client data.
Also the application user is required to access by default.
The expectation is:
- 2 data managers will have access to 4 database (excluding this client)
- 2 SME's will have access to 4 database (excluding this client)
- 1 application user will have access to all databases
- 1 assigned data managers will have access to all databases
- 1 assigned SME will have access to all databases
My knowledge on SQL Server 2016 security and roles are limited. Please suggest an approach for this requirement.