Authentication Modes in SQL SERVER
There are two types of authentication modes available in SQL SERVER 2005
1. Windows Authentication. Fromerly knows as NT authentication.
2. SQL Server Authentication
Note : Please note that windows authentication is always available as it does not depends on any configuration.
We have users and groups in windows and they are mapped to SQL SERVER.
When any user tries to log into the sql server , they are validated through the windows domain and mapped to the roles.Roles means what a user is allowed to perform.
SQL SERVER Authentication
Here you don’t have to map anything with windows groups and users.
One has to explicitly set an account here.One can create initial administrator account at the time of SQL Server and after than we can create as any accounts as we want.
Advantage here is that one user can have multiple logins that give different rights to different things.