If you use the Windows Authentication mode, you can use the user and group accounts available in the Windows domain for authentication. This lets domain users access databases without a separate SQL Server login ID and password. This is beneficial because this means that domain users do not have to keep track of multiple passwords, and if they update their domain password, they will not have to change SQL Server passwords as well. However, users are still subject to all the rules of the Windows security model, and you can use this model to lock accounts, audit logins, and force users to change their passwords periodically.

When you use Windows Authentication, SQL Server automatically authenticates users based on their user account names or their group membership. If you have granted the user or the user’s group access to a database, the user is automatically granted access to that database. By default, several local accounts are configured to use SQL Server. These accounts are the local Administrators group account and the local Administrator user account. (Administrator is included because it is a member of the Administrators group by default.) Local accounts are displayed as BUILTIN<AccountName>or COMPUTERNAME<AccountName> in SQL Server Management Studio. For example, Administrators is displayed as BUILTINAdministrators.

Real World

Domain accounts are the best way to manage users who access the database from within the organization. Also, if you assign users to domain groups and then configure access for these groups in SQL Server, you cut down on the amount of administration you have to do. For example, if you assign users in the marketing department to a marketing group and then configure this group in SQL Server, you have only one account to manage instead of 10, 20, 50, or more. When employees leave the organization or change departments, you do not have to delete user accounts. When new employees are hired, you do not have to create new accounts either—you only need to make sure that they are added to the correct group in Windows.

With mixed security, you use both Windows Authentication and SQL Server logins. SQL Server logins are primarily used by users outside the company, such as those who might access the database from the Internet. You can configure applications that access SQL Server from the Internet to use specific accounts automatically or to prompt the user for a SQL Server login ID and password.

With mixed security, SQL Server first determines if the user is connecting using a valid SQL Server login. If the user has a valid login and has the proper password, the user connection is accepted. If the user has a valid login but has an improper password, the user connection is refused. SQL Server checks the Windows account information only if the user does not have a valid login. In this case, SQL Server determines whether or not the Windows account has permission to connect to the server. If the account has permission, the connection is accepted. Otherwise, the connection is refused.

All SQL Server servers have the built-in sa login and may also have NETWORK SERVICE and SYSTEM logins (depending on the server instance configuration). All databases have built-in SQL Server users known as dbo, guest, INFORMATION_SCHEMA, and sys. These logins and users that are provided for special purposes are discussed in the following section titled "Special Purpose Logins and Users."