CREATE LOGIN (TSQL Statement)

creates a new login, so that a client can establish a connection to the server.

Syntax

CREATE LOGIN login_name WITH <option> [ ,... ]

<option> ::=
      PASSWORD         = 'password'
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

Arguments

login_name
is the login name.
PASSWORD = ‘password’
is the password used to authenticate the login when it connects to RSQL Server. It is a string.
DEFAULT_DATABASE = database
is the name of the default database to use just after connection is established.
DEFAULT_LANGUAGE = language
is the default language to use just after connection is established.
  • language is e.g. en_US, en_us, fr_CH, etc. French, english, german, etc are aliases for fr_FR, en_US, de_DE, etc.
  • If this clause is omitted, the default is the SERVER_DEFAULT_LANGUAGE parameter.

Remarks

Only sa is allowed to execute this statement.

A login is essentially used to establish a connection to the server, authenticated by its password.

A new login has no authorization to work on any database, even on its default database.

On server installation, the only existing login is sa, which is the system administrator of the server. It has all permissions on any database or object.

The command SHOW L displays the list of all logins.

Examples

USE mydb;

-- create a new login to connect to the server
CREATE LOGIN vladimir WITH PASSWORD='H3ll0_16660403';

-- a user is created in current database mydb, mapped to login vladimir, so that it can access mydb
CREATE USER vladimir; -- user vladimir is mapped to the login with the same name

-- grant permission on a table
GRANT SELECT ON clients TO vladimir;


CREATE LOGIN julius WITH PASSWORD='D13d15March44BC', DEFAULT_DATABASE=mydb, DEFAULT_LANGUAGE=italian;
CREATE USER gaius FOR LOGIN julius; -- user gaius is mapped to login julius
GRANT INSERT, SELECT, UPDATE, DELETE ON clients TO gaius;

SHOW L    -- same as SHOW LOGINS
SHOW U    -- same as SHOW USERS