Create Windows Login in SQL Server

In this article, we will show you the step by step approach to create Windows Login in SQL Server. There are two approaches to create Widows Login authentications: SQL Server Management Studio and Transact SQL query.

This article explains both these approaches with an example. Before we start creating new Windows login, let me show you the list of available logins in our SQL Server Management Studio

Create Windows Login in SQL Server Management Studio

In order to create 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

Once you select the New Login.. option,the following window will be opened. By default, Windows Authentication is selected so, you don’t need to change anything.

• Login Name: Please select the Windows User. This is the one that you are going to use to login to database Engine.

If you know the login name then type the name inside Login name text box otherwise, please click on the Search button beside the Login Name

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

If you want to change the Object Types then you can click the Object Types.. button

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

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

If you don’t know the name then click the Advanced button, and click Find Now button. This will display all the existing users

Default Database: Here you have to select default Database that you want to assign to this user. For now, we are selecting the SQL Test database.

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

Server Roles: You can 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.

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

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

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

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

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

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.

Status: I this tab we had two options:

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

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

Create Windows Login in SQL Server using T-SQL Query

The basic syntax to create Windows login is:

-- Create Windows Login in SQL Server Syntax
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 login Kishore with default settings.

SQL CODE

-- Create Windows Login in SQL Server Syntax
CREATE LOGIN [PRASAD\Kishore] -- This is the User that you use for login 
FROM WINDOWS

OUTPUT

Below screenshot will show our newly created login

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

Below code snippet will create a new login PRASAD\Kishore, and his default database is SQL tutorial.

SQL CODE

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

OUTPUT

You can see the same in its properties.

Edit Windows Login in SQL Server

In order to edit the existing widow’s logins, Please go to the Logins folder to select the required Login name.

Right-click on the widow’s login name that you want to edit, and select Properties option from the context menu.

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

Delete Windows Login in SQL Server using SSMS

Please expand the Logins folder to select the required windows Login name. Right-click on the name that you want to delete, and select Delete option from the context menu as we shown below.

Once you select Delete option from context menu, below shown window will be opened. Click OK to delete the windows login

Delete Windows Login in SQL Server using T-SQL

In this example, we will show you the SQL query to delete the windows login.

SQL CODE

-- Delete Windows Login in SQL Server
DROP LOGIN [PRASAD\Kishore]

OUTPUT

Thank You for Visiting Our Blog