SQL CREATE LOGIN

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.

CREATE LOGIN login_name WITH PASSWORD = 'strong_password' MUST_CHANGE;

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.

CREATE LOGIN [domain\login_name] FROM WINDOWS;

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:

CREATE LOGIN login_name WITH PASSWORD = 'strong_password',
DEFAULT_DATABASE=database_name, DEFAULT_LANGUAGE=language_name;

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.

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:

CREATE LOGIN my_login WITH PASSWORD = 'Password123!';

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:

GRANT CONNECT SQL TO my_login;
ALTER SERVER ROLE sysadmin ADD MEMBER my_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:

USE my_database;
CREATE USER my_user FOR LOGIN my_login;
ALTER ROLE db_datareader ADD MEMBER my_user;

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.