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 using SSMS
In order 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
Once you select the New Login.. option, following window will be opened. By default, Windows Authentication is selected so, let me change it to SQL Server Authentication.
- Login Name: Please use unique name. This is the one that you are going to use to login to database Engine.
- Password: Here you have to specify the password for the above specified user name.
- Enforce Password Policy: If you check mark this option, your password should follow the standard SQL Server password policy.
- Enforce Password Expiration: Password will expire after certain period.
- User Must Change password at Next Login: When the user login with this credential, SQL Server will ask to change the password.
As you can see from the below screenshot, we assigned the login name as John, and some random password.
Default Database: Here you have to select default Database name that you want to assign for this user. For now, we are selecting 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.
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. So, please refer the same for further reference. For now let me select the default Public option.
User Mapping: Use this 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 writ a dedicated article to explain about each individual database role. So, for now let me select the default Public, and db_Owner option.
Securables: This page is used to select the Object. Please click on the Search button to search for Objects.
For now, let me select the Server
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.
Status: It has 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 Login in SQL Server Management Studio.
Please click on the Connect button to connect the Database Engine using John
Please change the Authentication mode from Windows Authentication to SQL Authentication, and provide the Username ad Password.
You can see from the below screenshot, we had successfully logged into database engine using our newly created login.
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
Below code snippet will create a new login Mahesh with default settings.
-- Create SQL Login Example CREATE LOGIN Mahesh WITH PASSWORD = 'password'
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 master database.
Below code snippet will create a new login Rob, and his default database is SQL tutorial, and 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
You can see the same in its properties.
Below code snippet will create a new login Christy.
- Her password must change when she start login,
- Password will expire after certain period of time.
- Given password should meet the standard password policy.
- And her default database is SQL tutorial, and 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
Below screenshot will show the Christy in the logins section
You can see the login properties by going into its properties.
Let me try to connect with Christy
Once you click on the Connect button, SSMS will ask you to enter new password. This is because, we used the MUST_CHANGE in our query. This will happen if you check mark the User Must Change password at Next Login option also
Edit SQL Server Login
In order to edit the existing logins, Please go to the Logins folder to select the required Login name. Right-click on the 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 Server Roles, User mappings, Status, Securables etc.
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 the context menu as we shown below.
Once you select Delete option, below shown window will be opened. Click OK to delete the login
Delete SQL Server Login using T-SQL
In this example we will show you the SQL query to delete the SQL login.
-- Drop SQL Login DROP LOGIN Rob
From the below screenshot you can see that there is o Rob user in our SQL Server Logins
Thank You for Visiting Our Blog