In this chapter, we discussed how to secure access to a SQL Server instance, its databases, and objects inside a database. It is important to understand the role of each security element in the security hierarchy in a SQL Server system. The first step is to enable SQL Server to accept network connections, without which there would be no access to the system.
Once the physical network is possible, you must enable users to connect to a SQL Server instance. You can perform this task by creating logins for Windows users and groups and SQL Server logins. Before doing so, decide what authentication method the system should use.
Getting access to a SQL Server instance only gives access to perform specific server-wide operations, for which you can apply specific permissions through the use of fixed server roles or through assigning specific permissions to the logins.
In order to get access to a specific database, it is necessary to create users on that database. These users are typically mapped to a specific login in SQL Server. Once a user exists in a database, you can apply specific permissions for database-wide operations.
You control a user's access to database objects in different ways, depending on the type of object. These permissions can be applied to individual users or to database roles, which can either be fixed or be created on demand to satisfy business needs.
It is better to assign permissions to programmable objects, instead of tables and columns, to provide for easier maintenance and encapsulation.
Controlling the execution context also provides a way of letting users perform operations that can't be controlled through permissions.
Chapter 2 Quick Reference