SQL Server 2016 Security - Restrict users from viewing a single database

Question

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:

  1. Data manager - modify tables according to data, load data, execute SP
  2. Application user - used by front end web application
  3. 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:

There are

• 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.

Answer 1

Contained Databases are your option. Your server version supports them.

No one of your user will require server access, all of them can be authenticated by corresponding database. As consequence, none of them will "see" databases other than they are mapped too.

The problem of "seeing" all the databases derives from VIEW ANY DATABASE server level permission granted to public, if you revoke it from public or deny to someone, that login will not "see" even the database it is mapped to, except for the database owners (not to be confused with members of db_owner database role, the owner is only one server principal that owns the database).

So when we have no logins, we have no problem of databases visible to everyone