SQL CREATE LOGIN
Published
1. Introduction
The CREATE LOGIN
statement in SQL
is a fundamental command used to establish a new login, which is the identity used to connect to a SQL Server instance. This process is critical for managing access and security within the database
environment. A login represents a security principal, an entity that can be authenticated by the server. It is essential to distinguish between a login and a user. A login authenticates at the server level and grants the ability to connect to the SQL Server instance, whereas a database user is mapped to that login and grants permissions within specific databases. The CREATE LOGIN
command supports various authentication methods, including SQL Server authentication, Windows authentication, and Microsoft Entra authentication, catering to diverse security needs. Understanding how to create and manage logins is vital for maintaining a secure and well-organized database system. This article will delve into the intricacies of the CREATE LOGIN
command, covering its syntax, available options, and practical examples.
2. Basic Syntax and Options
The basic syntax of the CREATE LOGIN
command involves specifying the login name and the authentication method. The command structure allows for several configurable options, enhancing the flexibility and security of the login creation process. These options include setting passwords, defining default databases and languages, and enforcing password policies. It's crucial to understand each option to effectively manage logins. For example, when creating a SQL Server login, a password must be provided, and options such as MUST_CHANGE
, CHECK_EXPIRATION
, and CHECK_POLICY
can be used to enforce password policies. The FROM
clause specifies the authentication method, whether it is WINDOWS
, EXTERNAL PROVIDER
, CERTIFICATE
, or ASYMMETRIC KEY
.
SQL Server Authentication
When using SQL Server authentication, the PASSWORD
option is mandatory. This option specifies the password for the login being created. It’s best practice to use strong passwords that meet complexity requirements. Beginning with SQL Server 2012, passwords are stored using SHA-512 hashing. Additionally, for SQL Server authentication logins only, the MUST_CHANGE
option forces the user to change their password upon the first login. The CHECK_EXPIRATION
and CHECK_POLICY
options, which enforce password expiration and Windows password policies respectively, apply only to SQL Server authentication logins and are not relevant for Windows or external provider logins. These options are essential for maintaining a secure environment. The following code snippet shows basic syntax of creating a SQL Server login.
This example creates a SQL Server login with a password that the user must change upon first login. The HASHED
option can be used when migrating pre-existing hashed passwords from another SQL Server instance. It should not be used for creating new logins, as doing so bypasses SQL Server’s own password hashing and policy enforcement mechanisms.
Windows Authentication
For Windows authentication, the FROM WINDOWS
clause is used, mapping the login to a Windows domain account. The syntax for Windows-based logins requires the domain and login name to be in the format [<domainName>\<login_name>]
. This method uses the existing Windows security infrastructure, simplifying login management for organizations using Active Directory. Windows logins can also contain a backslash \
. In modern Active Directory environments, Windows login names (domain\username) can typically be much longer than 21 characters. SQL Server supports up to 128 characters for login names. There is generally no specific 21-character limit for Active Directory logins enforced by SQL Server. The following code snippet shows how to create a login using Windows authentication.
This method of authentication is convenient for environments where Windows domain management is already in place. It centralizes user management and ensures consistency across the network. In contrast to SQL Server authentication, no password needs to be provided during login creation.
Other Authentication Methods
Besides SQL Server and Windows authentication, CREATE LOGIN
also supports authentication through external providers like Microsoft Entra ID (formerly Azure Active Directory). The FROM EXTERNAL PROVIDER
clause is used for this, allowing Microsoft Entra users, groups, and applications to be used for logins. This method (using FROM EXTERNAL PROVIDER
) is supported for Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2022 or later only when the instance is properly integrated with Microsoft Entra ID (formerly Azure AD), typically via Azure Arc. Simply installing SQL Server 2022 is not sufficient; additional configuration and integration steps are required to enable this functionality. In other words, simply having SQL Server 2022 is not enough; the instance must be connected to Azure AD through Azure Arc or a similar configuration. The command also supports certificate-based logins using the FROM CERTIFICATE
clause and asymmetric key-based logins using the FROM ASYMMETRIC KEY
clause. However, these are primarily used for code signing rather than direct connections to SQL Server.
3. Managing Login Properties
When creating a login, several properties can be set to manage the login's behavior and access. These properties include the default database, default language, and security settings such as password policies. The DEFAULT_DATABASE
option specifies the database that the login will connect to by default. If not specified, it defaults to the master
database. The DEFAULT_LANGUAGE
option sets the language for the login, which defaults to the server's language if not specified. These properties are essential for personalizing the user’s experience and ensuring that the login operates effectively within the database environment. It is important to configure these settings appropriately to ensure consistent behavior for all users.
Setting Default Database and Language
The default database and language settings can be specified using the DEFAULT_DATABASE
and DEFAULT_LANGUAGE
options respectively. These settings ensure that when a user logs in, they are automatically connected to the specified database and have their preferred language settings. The default database is where the user will be connected to upon logging in, and the default language determines the language used for system messages. These settings can enhance user experience by reducing connection setup time and language related issues. The following code snippet shows how to set the default database and language when creating a login:
This example demonstrates setting both the default database and language for a new login. These settings can be changed later using the ALTER LOGIN
command.
Password Policies and Security Settings
Security settings such as password policies are crucial for protecting database environments. The CHECK_EXPIRATION
and CHECK_POLICY
options control whether password expiration and Windows password policies are enforced. These options are essential for maintaining secure logins. By default, when creating a new SQL Server authentication login, CHECK_POLICY
is set to ON
—enforcing Windows password policy rules—and CHECK_EXPIRATION
is set to OFF
, meaning passwords do not expire unless explicitly enabled. When CHECK_POLICY
is ON
, SQL Server enforces the Windows password policy as configured in the operating system or domain. By default, this often requires complexity such as including characters from three out of four categories (uppercase, lowercase, number, special character). However, the exact rules depend on the Windows domain or local server policy settings. These policies help prevent unauthorized access by enforcing strong password requirements. The following table summarizes the key password policy options.
Option | Description |
---|---|
MUST_CHANGE | Forces the user to change their password upon first login. |
CHECK_EXPIRATION | Specifies whether password expiration policy should be enforced. |
CHECK_POLICY | Specifies whether Windows password policies should be enforced. |
These options are critical for ensuring that logins are secure and comply with organizational security standards.
4. Creating Logins via Different Tools
Logins can be created using various tools, including SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) commands. SSMS provides a graphical user interface that simplifies login creation, while T-SQL allows for programmatic creation and automation. The tool you use depends on your preference and requirements. Some users may prefer the visual interface of SSMS, whereas others may opt for using T-SQL for scripting and automation. Both methods are equally valid, and it is important to be proficient in using both methods.
Using SQL Server Management Studio (SSMS)
Using SSMS, you can create a new login by navigating to the Security folder, right-clicking on Logins, and selecting New Login. This opens the Login - New dialog box, where you can enter the login name, select the authentication method, set password options, and configure properties such as default database and language. The SSMS interface streamlines the process, making it accessible even for users who are new to SQL Server. SSMS also provides options for setting server roles, user mappings, securables, and status, providing a comprehensive approach to login creation. The image below shows the Login - New dialog box in SSMS.
This graphical interface allows administrators to easily configure a wide array of settings without writing any code. However, for more complex environments, scripting is often preferred.
Using Transact-SQL (T-SQL)
Creating logins using T-SQL involves executing the CREATE LOGIN
command with the desired options. This method is preferred for scripting and automation. It allows for more control over the login creation process and is easily repeatable. Using T-SQL allows for dynamic login creation and modification, making it ideal for large-scale deployments. The T-SQL method is also useful for backing up and restoring logins between different SQL server instances. Here is a simple T-SQL script to create a login:
This simple script creates a login called my_login
with the password Password123!
. This method is efficient for creating multiple logins and can be incorporated into scripts. T-SQL also supports all the options supported by SSMS, providing complete control over login creation.
5. Permissions and Roles
After creating a login, you need to assign the necessary permissions and roles for the login to perform specific tasks. These permissions determine what the login can do on the SQL Server instance and within individual databases. Permissions are granted to logins at the server level and to database users at the database level. Understanding how to grant permissions and assign roles to a login is crucial for maintaining a secure and functional database environment. It's important to ensure that users have only the minimum required permissions to do their job.
Server-Level Permissions
Server-level permissions control access to server-wide resources and functionalities. These permissions include the ability to connect to the server, create databases, and manage logins. Permissions are granted using the GRANT
statement and revoked using the REVOKE
statement. Server roles are groups of permissions that can be assigned to logins. Some common server roles include sysadmin
, securityadmin
, and dbcreator
. The sysadmin
role has full control over the SQL Server instance, while securityadmin
can manage logins and permissions, and dbcreator
can create new databases. The following code snippet grants server level permissions to a login:
This script grants the my_login
the permission to connect to the server and assigns it the sysadmin
role. These permissions should be granted carefully, ensuring only the necessary access is provided.
Database-Level Permissions
Database-level permissions control access to databases and objects within those databases. These permissions include the ability to read data, modify data, and execute stored procedures. Permissions are granted to database users, which are mapped to logins. It is essential to create a user within a database and assign it the necessary permissions. Some common database roles include db_owner
, db_datareader
, and db_datawriter
. The db_owner
role has full control over the database, while db_datareader
and db_datawriter
have read and write access respectively. Here is an example of creating a database user and granting permissions:
This script creates a user called my_user
for the login my_login
in the database my_database
and assigns it the db_datareader
role. This ensures that the login can access the specified database and has the required permissions. It's crucial to review and manage permissions regularly to maintain the security of the database environment.
6. Key Takeaways of SQL CREATE LOGIN
In summary, the CREATE LOGIN
command is a fundamental SQL statement for managing access to a SQL Server database. It allows for the creation of both SQL Server and Windows logins, as well as logins mapped to external providers, certificates, and asymmetric keys. The command offers various options for password management, enforcement of password policies, and setting default databases and languages. Whether you choose to create logins through SSMS or T-SQL, understanding the various configuration options is essential. Furthermore, after creating logins, it is important to assign proper permissions and roles to maintain a secure and functional database environment. The CREATE LOGIN
command is a cornerstone of database security and management, and proper use of it is essential for any SQL server administrator.
Learning Resource: This content is for educational purposes. For the latest information and best practices, please refer to official documentation.
Text byTakafumi Endo
Takafumi Endo, CEO of ROUTE06. After earning his MSc from Tohoku University, he founded and led an e-commerce startup acquired by a major retail company. He also served as an EIR at Delight Ventures.
Last edited on