Creating Users on Microsoft SQL Server

Create Users on MS SQL Server

A user is created for a database in SQL Server, whereas a login is created for the server instance. A Login must be linked to a user to connect to a database. For any database, a login can only be mapped to one user. The database is the scope of a user.

A new user can be created using two different methods:

1. Using T-SQL
2. Using SSMS(SQL Server Management Studio)
3. Using Window Authentication

Method 1: Using T-SQL

For the SQL server to add users to the database, you can use the T-SQL create user command. The syntax for the SQL create user command is as follows:

Create user <username> for login <loginname>

Example

Run the following query to generate the user name "TestUser" with the mapping to the login name "TestLogin" in the TestDB database.

create user TestUser for login TestLogin 

Method 2: SSMS(SQL Server Management Studio)

Before creating a user account, we should first create a login under any name. You can follow the steps mentioned above to create a new Login. Use the login name "TestLogin" for now.

1. Connect the SQL Server and expand the databases folder. Expand the security folder and the database called "TestDB," where we will create the user account. To view the next screen, right-click Users and select the new user.
   
   
   Source
    
2. In the user name field, type "TestUser," and then click the ellipse to choose the login name "TestLogin."
   
   
   Source
    
3. To display the login name, click OK. To create the "TestUser" user, click OK once again.
   
   
   Source
    

After following these steps, you can create users on Microsoft SQL Server.

Method 3: Window Authentication

The steps to creating a user using Window Authentication are as follows:

1. Open Object explorer in the SQL Server Management Studio. 
    
2. Click on Server_instance_name > Security > Logins.
    
3. Right-click on Logins and then select New Login.
    
4. Type the name of a new user in the Login name field on the General page.
    
5. Click on the Window Authentication. 
    
6. Choose one of the following tasks, depending on the role and permissions you want to give this user:
   
   
   • Assign the new login ID to the sysadmin role on the Server Roles page.
   • Grant minimum permissions to the user if you do not want to assign the sysadmin role to the user.
      

Must Read SQL Clauses

Frequently Asked Questions

What is the user in SQL Server?

A database-level security principal is a user. Logins must be associated with a database user to connect to a database. A login can only be mapped as one user in each database, even though it can be mapped to several databases as distinct users. A user without a login can be created in a database that is only partially contained.

Which type of user is the default user in SQL Server?

The default database user access mode is MULTI_USER. Any user with the authorization to access the database can do so in this database user access mode.

Name the different types of user accounts.

The different types of user accounts are: 

• System account
• Superuser account
• Ordinary user account
• Guest user account

Conclusion

This article extensively discussed the topic of Creating users on a Microsoft SQL server in detail. We started with an introduction and debated the definition of an MS SQL server. Later, we also discussed the methods of creating users in SQL Server. 

We hope this blog has helped you enhance your knowledge of Creating users on Microsoft SQL Server. If you want to learn more, check out our other articles on the topics like Azure SQL Server, Azure Synapse SQL, Operational Databases, and many more on our platform Coding Ninjas Studio.

Check out this problem - Smallest Distinct Window .

For peeps out there who want to learn more about Data Structures, Algorithms, Power programming languages, JavaScript, interview questions, or any other upskilling, please refer to our guided paths on Coding Ninjas Studio. Enroll in our courses, go for mock tests, solve available problems, and interview puzzles. Also, you can focus on interview stuff- interview experiences and an interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Happy Coding!