Skip to content
Tutorial Gateway
  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • MySQL
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
    • Search
Create SQL Server Login 22

Create SQL Server Login

This article will show you the step-by-step approach to creating a SQL Server Login using Management Studio and Transact query. Before we start creating a new one, let me show you the list of available logins in our Management Studio.

Create SQL Server Login using SSMS

To create a new one, please expand the SQL Server Security folder, and right-click on the Login folder to open the context menu. Please select the New Login.. option to create it.

Select New Option

Once you choose the New option, the following window will open. By default, Windows Authentication is selected, so let me change the login to SQL Server Authentication.

  • Login Name: Please use a unique name for this newly created one. It is the one you will use for the Database Engine.
  • Password: Here, you must specify the password for the above-specified username.
  • Enforce Password Policy: If you checkmark this option, your password should follow the standard password policy.
  • Enforce Password Expiration: The password will expire after a certain period.
  • User Must Change Password at Next Login: When the user sign-in with these newly created credentials, the Server will ask to change the password.
New Window 3

We assigned the newly created SQL Server Login name as John and some random passwords.

Default Database: Here, you must select the default Database name you want to assign for this user. For now, we are choosing the default database.

Default Language: You can select the language as per your requirements. For now, we are leaving it default, which is English.

Choose the Default Database for SQL Server Login

Server Roles: Use this page to assign the Server Roles to this SQL Server Login User. We have already explained everything about the Server roles in Create Roles. For now, let me select the default Public option.

Assign Server Roles to User 6

User Mapping: Use this SQL Server Login page to select the database and assign database roles to this User. Since we selected the below one as the default database, Login has automatically mapped the user to this database.

Again, We will write a dedicated article to explain each database role. So, for now, let me select the default Public and db_Owner options.

Map User to Database Role 7

Securables: This page is used to select the Object. Please click on the Search button to search for Objects.

Search for Securables 8

For now, let me select the Server.

Add the Server Instance Object 9

You can use the Grant, With Grant, and Deny options to grant permissions or to deny. For now, we are leaving it without any changes.

Created SQL Server Login permissions on instance 10

Status: ThisSQL Server Login tab or section has two options:

  • Permission to connect to database Engine: If you want to provide, then select Grant; otherwise, choose Deny.
  • Login: If you want this user as the Database Manager, select Enabled otherwise, Disabled.
Grant Permission 11

See our newly created Login in SQL Server Management Studio. Please click on the Connect button to connect the Database Engine using John

View Newly created Sql Server login in object explorer 13

Please change the Authentication mode from Windows Authentication and provide the Username and Password.

Created SQL Server Log in to connect to Database Engine

We successfully logged into the SQL Server database engine using our newly created login.

Available users in management studio 15

Create SQL Server Login using Transact Query

The basic syntax to create a login is:

-- Create Syntax
CREATE LOGIN [LoginName] -- This is the User 
WITH PASSWORD = 'provide_password' MUST_CHANGE,
CHECK_EXPIRATION = ON, -- This is Optional
CHECK_POLICY = ON, -- This is Optional
DEFAULT_DATABASE = [Database Name], -- This is Optional
DEFAULT_LANGUAGE = [Language Name];-- This is Optional

It will create a new one called Mahesh with default settings.

CREATE LOGIN Mahesh
WITH PASSWORD = 'password'

Run the above query

Messages
--------
Command(s) completed successfully.

It will show our newly created Mahesh.

Create SQL Server Login using query 16

Please go to its properties to see or edit the login properties. As you can see, this log-in is pointing to the master database.

Open properties Window 18

It will create a new signing Rob and his default database, and the default language is English.

CREATE LOGIN Rob
WITH PASSWORD = 'password', 
DEFAULT_DATABASE = [SQL Tutorial], -- This is Optional
DEFAULT_LANGUAGE = [English];-- This is Optional

You can see the same in its properties.

View User Default database and language 20

The below Sql Server code snippet will create a new login Christy.

  • Her password must change when she starts login,
  • The password will expire after a certain period.
  • A password should meet the standard password policy.
  • And here, we used the below one as the default database, and the default language is English.
CREATE LOGIN Christy
WITH PASSWORD = 'password' MUST_CHANGE, 
CHECK_EXPIRATION = ON, -- This is Optional
CHECK_POLICY = ON, -- This is Optional
DEFAULT_DATABASE = [SQL Tutorial], -- This is Optional
DEFAULT_LANGUAGE = [English];-- This is Optional
Create SQL Server Login 21

See Christy in the section.

View Newly Created User 22

You can see the properties by going into their properties.

Edit existing user 25

Let me try to connect with Christy.

Connect to Server using SQL Authentication login 23

Once you click on the Connect button, SSMS will ask you to enter a new password. It is because we used the MUST_CHANGE in our query. It only happens if you checkmark the User Must Change Password at the Next Login option also.

Change Password popup 24

Edit Login

Please go to the SQL Server Logins folder to edit the existing ones to select the required name. Right-click on the name and select the Properties option.

Properties of a created SQL Server Login 26

Use this Properties window to alter the Server Roles, User mappings, Status, Securables, etc.

Properties window 27

Delete SQL Server Login using SSMS

Please go to the Logins folder to select the required name. Right-click on the name that you want to delete and select the Delete option from it.

Delete SQL Server Login 28

Once you select the Delete option, the below-shown window will open. Click OK to delete the existing one.

Confirming Message

Delete Login using Transact Query

The SQL Server query to delete or drop Login.

DROP LOGIN Rob

Run the above query

Messages
--------
Command(s) completed successfully.

See that there is no Rob user.

Create SQL Server Login 31
Back To Categories SQL
Find all Tables that Contain Specific Column Name
Create Windows Login in SQL Server

Related Topics

  • SQL Server Tutorial
  • SQL Database
    • Create Database
    • Attach Database
    • Detach Database
    • Get Database Names
    • Restore Database
    • Available Data Types
    • Create Table
    • Add a New Column
    • Alter Table
    • Derived Table
    • Get Column Names From Table
    • Get Table Names in a DB
    • Global & Local Temp Table
    • Rename Column Name
    • Table Name and Column Name
    • Table Variable
  • SQL Basic Commands
    • ALIAS
    • BULK INSERT or BCP
    • DML, DDL, DCL & TCL Cmds
    • DELETE Statement
    • INSERT Statement
    • INSERT INTO SELECT
    • SELECT Statement
    • SELECT DISTINCT
    • SELECT INTO Statement
    • TRUNCATE Statement
    • UPDATE Statement
    • UPDATE from SELECT
    • Query Builder
  • SQL Clauses
    • Introduction to Clauses
    • GROUP BY Clause
    • Having Clause
    • ORDER BY Clause
    • TOP Clause
    • WHERE Clause
  • SQL Joins
    • Types of JOINS
    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • FULL JOIN
    • SELF JOIN
    • CROSS JOIN
    • Cross Join Vs Inner Join
  • SQL Operators & NULLs
    • AND & OR Operators
    • Arithmetic Operators
    • BETWEEN Operator
    • Comparison Operators
    • COALESCE Function
    • EXCEPT
    • EXISTS Operator
    • INTERSECT
    • IN Operator
    • IS NOT NULL
    • IS NULL Function
    • ISNULL
    • LIKE Wildcard
    • NOT EXISTS Operator
    • NOT IN Operator
    • UNION
    • UNION ALL
  • SQL Control Flow
    • IIF Function
    • IF ELSE
    • ELSE IF
    • WHILE LOOP
    • BREAK
    • CONTINUE
    • GOTO Statement
    • CHOOSE
  • SQL Constraints
    • Constraints Introduction
    • Check Constraint
    • Clustered Index
    • Default Constraint
    • Non-Clustered Index
    • Primary Key
    • Foreign Key
    • Referential Integrity
    • Unique Constraint
    • Filtered Indexes
  • Advanced SQL Topics
    • CASE Statement
    • CDC Change Data Capture
    • CTE Example
    • MERGE Statement
    • PIVOT
    • Subquery
    • Table Partitioning
    • TRY CATCH
    • TRANSACTIONS
    • ACID Properties
    • VIEWS
    • User Defined Functions
    • UNPIVOT
  • SQL Stored Procedures
    • Stored Procedures Introduction
    • INSERT Stored Procedure
    • SELECT Stored Procedure
    • UPDATE Stored Procedure
    • Input Parameters in SP
    • Output Parameters SP
    • Insert SP result into Temp Table
    • Return Values in SP
    • Useful System SPs
  • SQL Triggers & Cursors
    • Introduction to Triggers
    • AFTER DELETE Triggers
    • AFTER INSERT Triggers
    • AFTER UPDATE Triggers
    • Instead Of DELETE Triggers
    • INSTEAD Of INSERT Triggers
    • INSTEAD of UPDATE Triggers
    • Static Cursor
    • Dynamic Cursor
    • FORWARD_ONLY Cursor
    • FAST_FORWARD Cursor
    • KEYSET Cursor
  • SQL Management Studio
    • How to Install SQL Server
    • Install Management Studio
    • Install AdventureWorks DB
    • Introduction to SSMS
    • Connect SQL with cmd utility
    • Backup Database
    • Create SQL Server Login
    • Create SQL Server Roles
    • Create Windows Login
    • Maintenance Plan
    • SQL Server Login Error
    • Uninstall Management Studio
  • SQL Aggregate Functions
    • Introduction to Aggregate
    • AVG Function
    • CHECKSUM_AGG Function
    • COUNT Function
    • COUNT_BIG function
    • GROUPING Function
    • GROUPING_ID Function
    • MIN Function
    • MAX Function
    • STDEV Function
    • STDEVP Function
    • SUM Function
    • VAR Function
    • VARP Function
  • Convert & Rank Functions
    • Introduce Conversions
    • CAST Function
    • TRY CAST
    • CONVERT
    • TRY CONVERT
    • PARSE Function
    • TRY_PARSE Function
    • Introduction to Ranking
    • RANK Function
    • DENSE_RANK
    • PERCENT_RANK
    • NTILE
  • SQL Date Functions
    • Introduction to Dates
    • GETDATE Function
    • GETUTCDATE Function
    • DAY Function
    • MONTH Function
    • YEAR Function
    • DATEADD
    • DATEDIFF
    • DATENAME
    • DATEPART
    • EOMONTH
    • DATEFROMPARTS
    • DATETIMEFROMPARTS
    • DATETIME2FROMPARTS
    • DATETIMEOFFESETFROMPARTS
    • SMALLDATETIMEFROMPARTS
    • TIMEFROMPARTS
    • TODATETIMEOFFSET
    • SWITCHOFFSET
    • SYSDATETIMEOFFSET
    • SYSUTCDATETIME
    • SYSDATETIME
    • DATEFIRST
    • @@DATEFIRST
    • @@LANGUAGE
    • SET DATEFORMAT
    • SET LANGUAGE
    • sp_helplanguage
    • CURRENT_TIMESTAMP
    • ISDATE
  • SQL Math Functions
    • Introduction to Math Functions
    • ABS Function
    • COS Function
    • ACOS Function
    • SIN Function
    • ASIN Function
    • ATAN Function
    • TAN Function
    • CEILING
    • FLOOR Function
    • EXP Function
    • LOG Function
    • LOG10 Function
    • POWER Function
    • RAND Function
    • RADIANS Function
    • SIGN Function
    • SQRT Function
  • SQL String Functions
    • Introduction to Strings
    • ASCII Function
    • CHAR Function
    • CONCAT Function
    • CONCAT_WS Function
    • CHARINDEX Function
    • DIFFERENCE Function
    • FORMAT Function
    • LEFT Function
    • LEN Function
    • LTRIM function
    • NCHAR Function
    • PATINDEX Function
    • QUOTENAME Function
    • REPLICATE Function
    • REPLACE Function
    • REVERSE Function
    • RTRIM Function
    • SPACE Function
    • SOUNDEX function
    • STR Function
    • STRING_AGG Function
    • STRING_ESCAPE Function
    • STRING_SPLIT Function
    • STUFF Function
    • SUBSTRING Function
    • TRANSLATE Function
    • TRIM Function
    • UPPER & LOWER Functions
    • UNICODE Function
  • Stats & Config Functions
    • Introduce Statistical Functions
    • Introduction to Configurations
    • @@CONNECTIONS
    • @@CPU_BUSY
    • @@IDLE
    • @@IO_BUSY
    • @@LANGID
    • @@LANGUAGE
    • @@MAX_CONNECTIONS
    • @@MAX_PRECISION
    • @@PACK_SENT
    • @@PACKET_ERRORS
    • @@PACK_RECEIVED
    • @@SERVICENAME
    • @@SERVERNAME
    • @@SPID
    • @@TIMETICKS
    • @@TOTAL_ERRORS
    • @@TOTAL_READ
    • @@TOTAL_WRITE
    • @@VERSION
  • Sys & Analytic Functions
    • Introduce System Functions
    • Introduce Analytic Functions
    • @@IDENTITY
    • ERROR LINE
    • ERROR MESSAGE
    • ERROR NUMBER
    • ERROR SEVERITY
    • ERROR STATE
    • HOST_ID
    • HOST_NAME
    • ISNULL
    • ISNUMERIC Function
    • FIRST_VALUE
    • LAST_VALUE
    • CUME_DIST
    • LAG Function
    • LEAD Function
    • PERCENTILE_DISC
    • PERCENTILE_CONT
    • SET Permission Functions
  • XML & FAQ’s
    • FOR XML AUTO
    • FOR XML PATH
    • FOR XML RAW
    • List Of Interview Questions
    • Calculate Running Total
    • Create ODBC Connection
    • Check if a Table exists
    • Configure SQL Database Mail
    • Concatenate Rows into String
    • Count Records in a Group
    • CTE Vs Temp Vs Derived tables
    • DATE Format
    • DATEPART Vs DATENAME
    • Delete Duplicate Rows
    • Extract Domain From Email
    • Export Data From SQL to CSV
    • Find the Table Dependencies
    • Find Nth Highest Salary
    • Find all Tables that Contain Specific Column Name
    • Get Last Record from each Group
    • IDENTITY INSERT
    • Identity Value Jumps after restarting SQL Server
    • Insert Images into SQL Server
    • Replace a String
    • Reverse String
    • Return Date Part Only from a Datetime datatype
    • Rows with Max Column Value
    • Swap Column Values
    • Standard Date Time Format Strings
    • Select First Row in each group
    • Select All If Parameter is Empty or NULL
    • Top N Records from each Group
    • UNION Vs UNION ALL
  Copyright © 2023. All Rights Reserved.
Home | About | Contact | Privacy Policy