In this post explains how to use the SQL Server CREATE LOGIN statement with syntax and examples.
The CREATE LOGIN statement creates an identity used to connect to a SQL Server instance. The Login is then mapped to a database user (so before creating a user in SQL Server, you must first create a Login).
There are four types of Logins that you can create in SQL Server:
The syntax for the CREATE LOGIN statement using Windows Authentication is:
OR
The syntax for the CREATE LOGIN statement using SQL Server Authentication is:
OR
The syntax for the CREATE LOGIN statement using a certificate is:
OR
The syntax for the CREATE LOGIN statement using an asymmetric key is:
The name of the Windows domain account.
The name of the Login.
The default database to assign to the Login.
The default language to assign to the Login.
By default, it set to OFF. This option determines whether password expiration policy is enforced. You must specifiy CHECK_EXPIRATION = ON when you use the MUST_CHANGE option.
The password to assign to the Login.
The hashed value of the password to assign to the Login.
It is used when you want to force the password to be changed the first time that the Login is used.
The GUID of the login. If this parameter is omitted, SQL Server will assign a GUID to the Login.
The name of a credential to assign to the Login.
The name of the certificate to assign to the Login.
The name of an asymmetric key to assign to the Login.
Let's look at how to create a Login using Windows Authentication in SQL Server (Transact-SQL).
For example:
This CREATE LOGIN example would create a new Login called [test_domain\AODBA] that uses Windows authentication.
Next, let's look at how to create a Login using SQL Server Authentication.
For example:
This CREATE LOGIN example would create a new Login called AODBA that uses SQL Server authentication and has a password of 'pwd123'.
If we want to force the password to be changed the first time that the Login is used, we could modify our example as follows:
This example uses the MUST_CHANGE option to force the password to be changed on the first login. It is important to note that the MUST_CHANGE option cannot be used when the CHECK_EXPIRATION is OFF.
Therefore, this example also specifies "CHECK_EXPIRATION = ON". Otherwise, the CREATE LOGIN statement would raise an error.
Let's look at how to create a Login from a certificate in SQL Server (Transact-SQL).
For example:
This CREATE LOGIN example would create a new Login called AODBA that uses a certificate called certificate1.
Let's look at how to create a Login from an asymmetric key in SQL Server (Transact-SQL).
For example:
This CREATE LOGIN example would create a new Login called AODBA that uses an asymmetric key called asym_key1.