CREATE LOGIN

Creates a login — an account that can connect to the server. Querona keeps a single tier of principals: a login is a user, it belongs to the server as a whole, and it is visible from every virtual database. There is no separate database user to create afterwards.

A login is created either with a password, which Querona verifies itself, or from Windows, in which case the operating system or the directory authenticates the account and Querona only recognises the name.

Syntax

Arguments

login_name

The name of the login. It must be unique across logins and roles — a single tier of principals means a role name is as unavailable as another login’s name. At most 128 characters. A name containing a backslash is reserved for Windows principals and is rejected for a password account (error 15006).

WITH PASSWORD = ‘password‘

Creates an account that authenticates with a password. The password is stored as a salted, key-derived hash, never in clear text, and it is redacted wherever statement text is logged, traced or audited. It may not be empty (error 33062); Querona enforces no other password rule.

FROM WINDOWS

Creates a Windows principal — a domain or local user, or a group. The name must be fully qualified as DOMAIN\name; an unqualified name is rejected (error 15407). Members of a group login authenticate through that group, exactly as the built-in BUILTIN\Administrators account works.

Remarks

The new login is created as an End User and is a member of public implicitly, like every principal. It holds no other permission until one is granted — see Access rights — and its user type can be changed afterwards in ADMINISTER ‣ User management.

Creating a login requires the Create login permission, which the securityadmin role holds. A caller without it is refused with error 15247.

Licensing is enforced when the account is created: a named-user or concurrent-user license refuses a login that would exceed its limit, and refuses a Windows group as a Power User or End User. The refusal names the limit that was reached.

CREATE LOGIN is server-wide: it may be executed from any database and it takes effect on every node of the cluster.

Differences from SQL Server

Behaviour	Querona
The Windows principal is verified against the directory	No. The name is recorded as given, and it is resolved when the account first authenticates. SQL Server refuses an unknown account with error 15401.
The user type can be chosen in the statement	No. Every login created with CREATE LOGIN is an End User; change the type in user management.
Password policy options (CHECK_POLICY, CHECK_EXPIRATION, MUST_CHANGE)	Not supported — Querona has a single password rule, that a password may not be empty, and no expiry. The options are rejected by name.
HASHED, SID, CREDENTIAL, DEFAULT_DATABASE, DEFAULT_LANGUAGE	Not supported; rejected by name.
FROM CERTIFICATE, FROM ASYMMETRIC KEY, FROM EXTERNAL PROVIDER	Not supported; rejected by name.
The statement is transactional	No. The account is created immediately, and a surrounding ROLLBACK does not remove it. In SQL Server the creation is rolled back with the transaction.
A Windows group is reported as G/WINDOWS_GROUP	No. sys.server_principals reports every integrated principal as U/WINDOWS_LOGIN.
CREATE USER	Not supported, and not needed: a login is already the user. Scripts ported from SQL Server drop the CREATE USER statement that follows CREATE LOGIN.
ALTER LOGIN	Not supported yet. Accounts are edited in user management; to remove one, see DROP LOGIN.

Examples

Create an account that Querona authenticates.

CREATE LOGIN jkowalski WITH PASSWORD = 'S3cret!';

Create a domain account. It authenticates over the SQL endpoint with integrated authentication.

CREATE LOGIN [CONTOSO\jkowalski] FROM WINDOWS;

Create a login for a domain group; every member of the group can then connect.

CREATE LOGIN [CONTOSO\Analysts] FROM WINDOWS;

Create an account and grant it a role in the same script.

CREATE LOGIN [CONTOSO\integration_svc] FROM WINDOWS;
ALTER SERVER ROLE datareader ADD MEMBER [CONTOSO\integration_svc];

Check the result.

SELECT name, type, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'CONTOSO\integration_svc';

Errors

Error	Raised when
103	The login name is longer than 128 characters.
15006	A password account’s name contains a backslash, which is reserved for Windows principals.
15025	A login or a server role already holds the name.
15247	The caller does not hold the Create login permission.
15407	A Windows principal was named without its domain.
33062	The password is empty.

Permissions

Requires the Create login permission, held by the securityadmin role.