As a DBA working with SQL server and managing SQL Server environments, I have created tons of users in SQL server. It is crucial to know how to do this task smoothly. In this article, I will walk you through the simplest approaches I used to follow to create SQL server user.
How To Create A User In SQL Server
Before starting, let me clarify that you can create 2 types of uses.
- SQL Server Authentication Users: Authenticated by SQL Server.
- Windows Authentication Users: Authenticated by Windows before SQL server.
Approach-1: Creating a SQL Server Authentication Users
To create a user in SQL Server, follow the steps below.
1. The first step is to create a login for our SQL Server instance using the SQL query below.
USE master;
GO
CREATE LOGIN rajusa WITH PASSWORD = 'Raj@123';The query was executed successfully, as shown in the screenshot below.
2. The next step is to create a user in that database that maps to this login using the below query.
USE Test;
GO
CREATE USER rajusa FOR LOGIN rajusa;The command was executed successfully, as shown below.
So, in the above example, we created a rajusa user.
It is very important to cross-check whether the user was created successfully. Let’s execute the query below to check that.
USE Test;
GO
SELECT name, type, type_desc FROM sys.database_principals WHERE name = 'rajusa';After executing the above query, you can see that the user created successfully. Check out the screenshot below for your reference.
Check out Grant Write Access To Table In SQL Server
Approach-2: Using SQL Server Management Studio (SSMS)
Follow the below steps.
1. Connect to SQL Server Management Studio.
2. Expand the Database node and select your database, expand your selected database node.
3. Right-click on the Security folder –> Select New –> Click on the User option as shown in the below screenshot.
4. Select the required User type. Enter the user name and the default schema, and then click the OK button, as shown in the screenshot below.
Now, to cross-check if the user created successfully, we can run the query below. As shown in the screenshot below, the user created successfully.
Check out How To Enable SA Account In SQL Server
Approach-3 Creating a Windows Authentication User
Syntax
USE DBName;
GO
CREATE USER [DOMAIN\UserName] FOR LOGIN [DOMAIN\UserName];Example
In the example below, we created the SQLWriter user.
USE Test;
GO
CREATE USER [NT SERVICE\SQLWriter] FOR LOGIN [NT SERVICE\SQLWriter];The query was executed successfully, as shown in the screenshot below.
To ensure that the Windows user is created successfully, let us execute the select statement below. As shown in the screenshot below, the user with the type Windows_user created successfully.
Conclusion
Creating users in SQL Server databases is a crucial skill for a DBA or developer working in SQL server. You can utilize the approaches mentioned above to create an SQL user.
You may also like following the articles below.
- How to Alter Table Column to Allow NULL in SQL Server
- How to Find Table Dependencies in SQL Server
- How To Copy Table Schema In SQL Server
Grey is a highly experienced and certified database expert with over 15 years of hands-on experience in designing, implementing, and managing complex database systems. Currently employed at WEX, USA, Grey has established a reputation as a go-to resource for all things related to database management, particularly in Microsoft SQL Server and Oracle environments. He is a Certified Microsoft SQL Server Professional (MCSE: Data Management and Analytics) and Oracle Certified Professional (OCP), with Extensive database performance tuning and optimization knowledge and a proven track record in designing and implementing high-availability database solutions.