One question I have been asked is about SQL Server 2005 Logins.  Logins are the Server principles which give user’s access to SQL Server.  We can create Logins using the SQL Server Management Studio (SSMS) graphically or use the CREATE LOGIN statement.
The syntax for a windows login


CREATE LOGIN [Domain\User] FROM WINDOWS
The password and account policy is managed by Active Directory.


The biggest change is with SQL Server logins the syntax is


CREATE LOGIN login_name WITH PASSWORD=’password’

You can use the following options when you create the login
MUST_CHANGE The user should change the password at the next login.
CHECK_EXPIRATION SQL Server will check the Windows expiration policy for the SQL Server login.
CHECK_POLICY SQL Server will apply the local Windows password policy on SQL Server logins.


For full syntax see CREATE LOGIN (Transact-SQL)