Access to databases in SQL Server is managed mainly through two principals:
- Logins - Security principal that allows access to the instance.
- Users - Security principal linked to a login that allows access to a specific database.
By default, SQL Server follows the practice of least privilege, meaing that when you create these principals (further documentation on that here), they have minimal access to a database and the objects within that database. Initially, these principals will only be able to connect to an instance or database but nothing else, such as selecting or modifying data.
This means that you, as an administrator, will need to
GRANT permissions to principals that need access. For example, if you had a table and you wanted to give a user the ability to read data on that table:
GRANT SELECT ON table_foo TO user_bar;
Once this is in place, your user can select from
table_foo, but do nothing else. You can further grant additional permissions to other users as necessary, or
REVOKE/DENY permissions as well. See here for further explanation of this.
Finally, you can leverage roles to additionally manage your security. Roles are essentially containers that have permissions granted to them and you can assign users to these roles, much like Active Directory groups. SQL Server has several server and database roles that are available by default and are commonly used, but you can also create custom database level roles and assign specific permissions to those roles.
Overall, database security is a large topic that is outside the scope of a StackExchange answer, but the basic idea is you can manage a "whitelist" by assigning specific permissions on your database objects and tightly controlling the security around those objects.