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 Windows Login 10

Create Windows Login in SQL Server

There are two approaches to create SQL Server Widows Login authentications: Management Studio and Transact query. This article explains both these approaches with an example. Before we start creating new windows login in SQL Server, let me show you the list of available logins in our Management Studio.

Create Windows Login 1

Create Windows Login in SQL Server Management Studio

To create SQL Server Windows 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.

  • Guide to Management Studio, Install SSMS, and Uninstall SSMS.
Create Windows Login 2

Once you choose the New Login.. option, the below-shown window will open. By default, Windows Authentication selected by SQL Server, and you don’t need to change anything.

  • Login Name: Please select the Windows User that you are going to use to login to the Database Engine.
Create Windows Login 3

If you know the login name, type the name inside the Login name text box. Otherwise, click on the Search button beside the Login Name

Create Windows Login 4

Once you click on the Search button, the following window will open.

Create Windows Login 5

If you want to change the Object Types, click the Object Types.. button

Create Windows Login 6

If you know the name, write it inside the text box and click Check Names button

Create Windows Login 7

As you can see, it is automatically retrieving the windows account information

Create Windows Login 8

If you don’t know the name, click the Advanced button and click the Find Now button. It displays all the existing users

Create Windows Login 9

Default Database: Select the default Database that you need to assign to this Windows Login user and for now, we are choosing the SQL Test database.

Create Windows Login 10

Default Language: Select the default language. For now, let us keep it default, which is English.

Create Windows Login 11

Server Roles: Use this to specify the Server Roles. Please refer to the Create Server Roles article. For now, we chose the default Public option.

Create Windows Login 12

User Mapping: Use this to pick the database and assign database roles. Since we chose the SQL Test as the default database, Windows Login has automatically mapped the user to this database.

Let me select the database roles as default Public, db_datareader, and db_Owner option.

Create Windows Login 13

Securables: Use the Search button to search for Objects.

Create Windows Login 14

For now, we are selecting the All Objects of the types.. option

Create Windows Login 15

And then, we are choosing the Server Roles. It means this particular server role will control all the server roles.

Select Server Object Type 16

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

Create Windows Login 17

Status: In this tab we had two options:

  • Permission to connect to database Engine: If you want to give, then select the Grant; otherwise, Deny.
  • Login: If you need this user to login to Database Manager, pick Enabled otherwise, Disabled.
Grant Permissions 18

From the below screenshot, you can see our newly created widows Login in SQL Server.

Create Windows Login

The basic syntax to create Windows login in SQL Server is:

CREATE LOGIN [Domain_Name\Login_Name] -- This is the User that you use for login 
FROM WINDOWS
DEFAULT_DATABASE = [Database Name], -- This is Optional
DEFAULT_LANGUAGE = [Language Name];-- This is Optional

Below code snippet will create a new windows login Kishore in SQL Server using CREATE LOGIN with default settings.

CREATE LOGIN [PRASAD\Kishore] -- This is the User that you use for login 
FROM WINDOWS

Run the above create login query

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

Below screenshot will show our newly created window login

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

Below code snippet will create a new windows login PRASAD\Kishore, and his default database is a shown below.

CREATE LOGIN [PRASAD\Kishore] -- This is the User that you use for login 
FROM WINDOWS
DEFAULT_DATABASE = [SQL Tutorial] -- This is Optional

Execute the above create login for windows query

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

You can see the same in its properties.

Edit Windows Login in SQL Server

To edit the existing SQL Server widows login, Please go to the Logins folder to select the required Login name. Right-click on the widows login name that you want to edit and select Properties option.

Once you pick the Properties option, the following window will open. Use this windowpane to alter the Default Language, Default Database, User mappings, Server Roles, Securables, and Status.

Login properties 28

Delete Windows Login in SQL Server using SSMS

Please extend the SQL Server Logins folder to choose the required windows Login name. Right-click on the name that you want to delete and select Delete option from the context menu.

Select Delete Option 23

Once you select the Delete option, the below-shown window opened. Click OK to delete the windows login

Delete Windows Login 24

Delete Windows Login in SQL Server using DROP LOGIN

In this example, we show you how to use the DROP LOGIN statement to delete the windows login in SQL Server.

DROP LOGIN [PRASAD\Kishore]

Execution result

Messages
--------
Command(s) completed successfully.
Back To Categories SQL
Create SQL Server Login
Create SQL Server Roles

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