Authentication and authorization

SQL Server security works in layers. As a first step, SQL Server will perform authentication, where SQL Server is determining who you are and if you can log in. If you're successfully logged on, then SQL Server will perform authorization, determining if you can do what you're trying to do. In the next part of the chapter, we will see how to configure the server authentication, how to work with server objects, and how to assign server-level permissions.

Authentication

SQL Server comes with two authentication modes--SQL Server and Windows Authentication mode and Windows Authentication mode. You can choose the authentication mode during installation and you can always change the mode afterward in the SQL Server configuration, which requires a service restart after the change. The following image gives a good idea of authentication options:

If the Windows Authentication mode is turned on, then SQL Server can use several different principals to evaluate your access...