What are the various security modes for SQL Server and how do they work?

Since SQL Server 7 - Microsoft has only supported two modes:

• Windows Only - Only Windows accounts can access the server via an integreated login process.
• Windows and SQL Server - Both Windows accounts and accounts created within SQL Server can connect to the server.

Windows Only is recommended but not always practical depending on your infrastructure.  In addition, SQL Server 2005 has taken great strides to increase the security of standard SQL Server logins by adding password complexity, timeouts, and other features previously absent.

Logging-in is only the first step. Once a user logs in, he or she must access the individual databases. For this, an entry must exist in the sysusers table for each database for that user. Keep a close eye on that “guest” account in your databases and make sure you don’t inadvertently give someone access to a database.