Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • 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

Create SQL Server Login

by suresh

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

Create SQL Server Login 1

Create SQL Server Login using SSMS

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

Create SQL Server Login 2

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

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

We assigned the login name as John and some random password.

Create SQL Server Login 4

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

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

Create SQL Server Login 5

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

Create SQL Server Login 6

User Mapping: Use this SQL Server Login page to select the database, and assign database roles to this User. Since we selected the SQL tutorial 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 option.

Create SQL Server Login 7

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

Create SQL Server Login 8

For now, let me select the Server

Create SQL Server Login 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.

Create SQL Server Login 10

Status: It 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 to login to Database Manager, select Enabled otherwise, Disabled.
Create SQL Server Login 11

See our newly created Login in SQL Server Management Studio.

Create SQL Server Login 12

Please click on the Connect button to connect the Database Engine using John

Create SQL Server Login 13

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

Create SQL Server Login 14

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

Create SQL Server Login 15

Create SQL Server Login using T-SQL Query

The basic syntax to create SQL login is:

-- Create SQL Login Syntax
CREATE LOGIN [Login Name] -- This is the User that you use for login 
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 login Mahesh with default settings.

-- Create SQL Login Example
CREATE LOGIN Mahesh
WITH PASSWORD = 'password'
Create SQL Server Login 16

It will show our newly created login

Create SQL Server Login 17

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.

Create SQL Server Login 18

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

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

You can see the same in its properties.

Create SQL Server Login 20

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

  • Her password must change when she starts login,
  • Password will expire after a certain period.
  • Given a password should meet the standard password policy.
  • And her default database is the SQL tutorial, and the default language is English.
-- Create SQL Login Example
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 the Christy in the logins section

Create SQL Server Login 22

You can see the login properties by going into their properties.

Create SQL Server Login 25

Let me try to connect with Christy

Create SQL Server 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 Next Login option also

Create SQL Server Login 24

Edit SQL Server Login

To edit the existing Sql Server logins, Please go to the Logins folder to select the required Login name. Right-click on the login name and select Properties option.

Create SQL Server Login 26

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

Create SQL Server Login 27

Delete SQL Server Login using SSMS

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

Create SQL Server Login 28

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

Create SQL Server Login 29

Delete SQL Server Login using T-SQL

The query to delete the SQL Server login.

-- Drop SQL Login
DROP LOGIN Rob
Create SQL Server Login 30

See that there is no Rob user in our SQL Server Logins

Create SQL Server Login 31

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy