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.

If this clause is omitted, the default is the SERVER_DEFAULT_DATABASE parameter.

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.

So, the next step is to create a user in the database, mapped to the login. See CREATE USER.
Also, to allow this user to access a table, permissions must be granted. See GRANT permissions.

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.

On installation, the default database for sa is trashdb, which it enters as user dbo.

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

Guide