10/9/2014 SQL Server (Transact-SQL): CREATE LOGIN statement
[Link] 1/4
Home > SQL Server (Transact-SQL) > Logins and Users
SQL SERVER (TRANSACT-SQL): CREATE LOGIN STATEMENT
Learn how to use the SQL Server CREATE LOGIN statement with syntax and examples.
DESCRIPTION
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:
1. You can create a Login using Windows Authentication.
2. You can create a Login using SQL Server Authentication.
3. You can create a Login from a certificate.
4. You can create a Login from an asymmetric key.
SYNTAX
The syntax for the CREATE LOGIN statement using Windows Authentication is:
CREATE LOGIN [domain_name\login_name]
FROM WINDOWS
[ WITH DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name ];
OR
The syntax for the CREATE LOGIN statement using SQL Server Authentication is:
CREATE LOGIN login_name
WITH PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , SID = sid_value
| DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name
| CHECK_EXPIRATION = { ON | OFF }
| CHECK_POLICY = { ON | OFF }
| CREDENTIAL = credential_name ];
OR
The syntax for the CREATE LOGIN statement using a certificate is:
CREATE LOGIN login_name
FROM CERTIFICATE certificate_name;
OR
10/9/2014 SQL Server (Transact-SQL): CREATE LOGIN statement
[Link] 2/4
The syntax for the CREATE LOGIN statement using an asymmetric key is:
CREATE LOGIN login_name
FROM ASYMMETRIC KEY asym_key_name;
Parameters or Arguments
domain_name is the name of the Windows domain account.
login_name is the Login name.
database_name is the default database to assign to the Login.
language_name is the default language to assign to the Login.
CHECK_EXPIRATION is by default 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.
password is the password to assign to the Login.
hashed_password is the hashed value of the password to assign to the Login.
MUST_CHANGE is used when you want to force the password to be changed the first time that the
Login is used.
sid_value is the GUID of the login. If this parameter is omitted, SQL Server will assign a GUID to the
Login.
credential_name is the name of a credential to assign to the Login.
certificate_name is the name of the certificate to assign to the Login.
asym_key_name is the name of an asymmetric key to assign to the Login.
NOTE
See also the ALTER LOGIN statement and the DROP LOGIN statement.
EXAMPLE - WINDOWS AUTHENTICATION
Let's look at how to create a Login using Windows Authentication in SQL Server (Transact-SQL).
For example:
CREATE LOGIN [test_domain\techonthenet]
FROM WINDOWS;
This CREATE LOGIN example would create a new Login called [test_domain\techonthenet] that
uses Windows authentication.
10/9/2014 SQL Server (Transact-SQL): CREATE LOGIN statement
[Link] 3/4
EXAMPLE - SQL SERVER AUTHENTICATION
Next, let's look at how to create a Login using SQL Server Authentication.
For example:
CREATE LOGIN techonthenet
WITH PASSWORD = 'pwd123';
This CREATE LOGIN example would create a new Login called techonthenet 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:
CREATE LOGIN techonthenet
WITH PASSWORD = 'pwd123' MUST_CHANGE,
CHECK_EXPIRATION = ON;
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.
EXAMPLE - CERTIFICATE
Let's look at how to create a Login from a certificate in SQL Server (Transact-SQL).
For example:
CREATE LOGIN techonthenet
FROM CERTIFICATE certificate1;
This CREATE LOGIN example would create a new Login called techonthenet that uses a certificate
called certificate1.
EXAMPLE - ASYMMETRIC KEY
Let's look at how to create a Login from an asymmetric key in SQL Server (Transact-SQL).
For example:
CREATE LOGIN techonthenet
FROM ASYMMETRIC KEY asym_key1;
This CREATE LOGIN example would create a new Login called techonthenet that uses an
asymmetric key called asym_key1.
10/9/2014 SQL Server (Transact-SQL): CREATE LOGIN statement
[Link] 4/4