CREATE LOGIN

Create a SQL Server login account (User).

Syntax
      CREATE LOGIN login {WITH PASSWORD = 'password'
                             [HASHED] [MUST_CHANGE]
                                [, option_list [ ,... ] ]}

      CREATE LOGIN login {FROM sources}

  sources:
      WINDOWS [ WITH windows_options [ ,... ] ]
      CERTIFICATE certname
      ASYMMETRIC KEY asym_key  

  option_list:
      SID = sid
      DEFAULT_DATABASE = database    
      DEFAULT_LANGUAGE = language
      CHECK_EXPIRATION = {ON | OFF}
      CHECK_POLICY = {ON | OFF}
      CREDENTIAL = credential 

  windows_options:
      DEFAULT_DATABASE = database
      DEFAULT_LANGUAGE = language

Key:
  login            Login: SQL Server / certificate-mapped / asymmetric key-mapped / [Windows].
  WINDOWS          Map to a Windows login.
  certname         Certificate to associate with this login.
  asym_key         Name of an asymmetric key to associate with this login. 
  password         A SQL Server login password for the login.
  HASHED           The SQL Server login is already hashed. (so don't hash again)
  MUST_CHANGE      Prompt the user for a new SQL Server password
  credential       Credential to be mapped to the new SQL Server login.
  sid              GUID of the new SQL Server login. (default=automatic)
  DEFAULT_DATABASE The default database to be assigned to the login.(default = master)
  DEFAULT_LANGUAGE The default language to be assigned to the login. 
  CHECK_EXPIRATION Enforce password expiration policy
  CHECK_POLICY     Enforce Windows password policy

Passwords are case-sensitive.

Prehashing of passwords is supported only when you are creating SQL Server logins.

If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON.

Examples

CREATE LOGIN [SERVER01\User01] FROM WINDOWS;
GO

CREATE LOGIN user42 WITH PASSWORD = 'pa$$word' MUST_CHANGE;
GO

"In the beginning, the universe was created. This has made a lot of people very angry and been widely regarded as a bad move" - Douglas Adams, The Hitchhiker's GuideTo The Galaxy.

Related commands:

ALTER LOGIN
CREATE USER
DROP LOGIN
EVENTDATA( )

Equivalent Oracle command:

CREATE USER