Modeblue

SQL Server 2005 has the same 3 types of logins as earlier versions of SQL Server. It also adds logins mapped to certificates and asymmetric keys.

SQL Server Login

The original type of login to a SQL Server. SQL Server stores the username and a hash of the password with the master database and verifies a login attempt to use a SQL Server login internally.

Because some applications and other operating systems are unable to connect using Windows authentication, SQL Server logins still have a role to play. Also, SQL Server logins allow connections from a Windows system in an untrusted domain or workgroup.

The password options with SQL Server logins have been increased. One of the negative aspects of SQL Server logins was the security weaknesses, however these have been addressed:

SQL Server 2005 has an option, MUST_CHANGE, which forces the user to change the password upon first successful use of the login.
SQL Server 2005 running on Windows Server 2003 can now require the same password complexity requirements set for the operating system (whether by local or Group Policy)
On Windows Server 2003, SQL Server 2005 can now handle password expiration according to the requirements set by the operating system (again through the local security policy or Group Policy).
SQL Server 2005 now locks out accounts after x attempts by checking the requirements set by the operating system.
These options are not a requirement, but the optional security measures are recommended where possible.

Windows / AD User and Group Logins

Using AD accounts to access SQL Server remains unchanged. User or group accounts must be local on the SQL Server or a trusted domain account. Users added to groups will need to login again to gain the SID of that group before authenticating on the SQL server.

Keep in mind that groups contain many users & the users can change. Only grant access to groups where the group administration is limited, and the Database is not a critical system.

Certificates and Asymmetric Keys

If a certificate or asymmetric key is stored within SQL Server 2005, it can be used as a login. This is a similar method to logging on to windows using a smart card.
Certificates and asymmetric keys aren’t used very heavily yet in SQL Server 2005.

Creating Logins

SQL Server 2005′s new syntax for creating logins is:
CREATE LOGIN name { WITH options | FROM source }

SQL Server Logins

PASSWORD = 'password' [HASHED] [MUST_CHANGE] [, additional options [, ...] ]
When creating a SQL Server login, the PASSWORD must be set. A blank password can be given by specifying anything between the single quotes, but obviously, this is not recommended from a security perspective. When specifying the password, two optional arguments may be specified:

HASHED specifies the password is already in a hash. (This is the same as specifying ‘skip_encryption’ as the value of the @encryptopt parameter for sp_addlogin.)
MUST_CHANGE will prompt the user to change the password on the first successful login.

DEFAULT_DATABASE and DEFAULT_LANGUAGE If you don’t specify these, SQL Server will set the login’s default database to master and the default language to the current default language of the server.

CHECK_EXPIRATION and CHECK_POLICY enforces the settings found in the computer’s effective local security policy. CHECK_POLICY is only fully enforced in Windows Server 2003. It can be turned on in Windows 2000, but only the password complexity is checked.

CREDENTIAL is an option which associates the login with what SQL Server 2005 calls a credential. A credential contains the authentication information (such as username and password) to connect to a resource outside of SQL Server. Since this is a basic article on logins, I won’t go into any more detail on credentials.
EXAMPLE:
CREATE LOGIN MatthewJenkins
WITH PASSWORD = 'MoDeBlUe' MUST_CHANGE,
DEFAULT_DATABASE = modeblueDB,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON

Windows Logins

The FROM source is for Windows-based logins, certificates, and asymmetric keys.
FROM WINDOWS [WITH Windows options [, ...] ]
FROM WINDOWS covers both Windows user accounts and Windows security groups. The Windows options are:
DEFAULT_DATABASE = default database
DEFAULT_LANGUAGE = default language

An example CREATE LOGIN command for a Windows account would be:

CREATE LOGIN [Modeblue\dba]
FROM WINDOWS
WITH DEFAULT_DATABASE = modeblueDB

Deleting Logins

This is very simple:
DROP LOGIN name

However, you can drop the login even if the login has been mapped into databases as a user.

Altering Logins

There are two ways to modify logins. The first is to enable or disable the login. The second is to make changes to the login’s properties. Both ways begin with ALTER LOGIN:

ALTER LOGIN name { status | WITH option [, ...] }

Enabling and Disabling Logins

A login may be set to one of two statuses:
ENABLE
DISABLE

ALTER LOGIN MatthewJenkins DISABLE

Setting Options

There are many options which can be used (and combined) with the ALTER LOGIN statement.

Resetting the Password

The password options are:
PASSWORD = ‘new password’ [ OLD_PASSWORD = 'old password' | secadmin password option [ secadmin password option ]
The two secadmin password options are: MUST_CHANGE and UNLOCK. The first forces the user to change the password upon first login. The second option unlocks a login which has been locked due to too many failed login attempts.

Example:

ALTER LOGIN MatthewJenkins WITH PASSWORD = 'm0d3blu3!' UNLOCK

Changing the Default Database or Language

Changing default database and/or language:
DEFAULT_DATABASE = database
DEFAULT_LANGUAGE = language

To set both options:
ALTER LOGIN MatthewJenkins
WITH DEFAULT_DATABASE = master,
DEFAULT_LANGUAGE = us_english

Renaming the Login

NAME = new login
So to rename user MatthewJenkins to MattJenkins:
ALTER LOGIN MatthewJenkins WITH name = MattJenkins

Checking Password Policy and Expiration

To check for password policy and expiration:

CHECK_POLICY = { ON | OFF }
CHECK_EXPIRATION = { ON | OFF }

Putting this together with a password reset we could execute the following:

ALTER LOGIN MatthewJenkins
WITH PASSWORD = 'mOdEbLuE!' MUST_CHANGE,
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON