When a user logs in with a user name and password from an untrusted connection,
SQL Server checks if such an SQL login exists. If it does, the login is successful,
if not the user cannot log in.
SQL Server authentication is intended for back compatibility (previous SQL
Server generations had only SQL Server authentication) and SQL Server instances
running on Windows 9x. In the latter case, SQL Server uses "mixed mode authentication"
(see below), but allows only SQL Server authentication.
Developers and database users are probably more familiar with SQL Server
authentication, because it employs the common "user account" system. SQL
Server authentication is required for non-local connections and non-Windows
NT clients.
SQL Server Authentication (a.k.a. mixed mode)
Enterprise Manager
- In Enterprise Manager, open the Properties dialog of the desired SQL
Server instance.
- Go to the Security tab.
- Select the option SQL Server and Windows NT.
- If required, set the Audit level option, which specifies what
login events are logged.
Roles in SQL Server have two tiers. One is the Server role
and the other Database role. It is recommended
to learn about Server logins first.