Welcome to another SQL Server tutorial. For this one, we will go over the process of creating various types of users in the SQL Server. We will learn how to utilize the CREATE USER statement to add a new type of user to the currently selected database. There are various types of users in the SQL Server. Without any hesitation, let us dive in.

SQL Server Create User Statement

SQL Server uses the CREATE USER statement to add a new user to the current database. However, the parameters may change depending on the type of user that you wish to create.

For example, the following syntax shows the command for creating a login user in the SQL Server:

Let us walk through the process and commands to create various user types.

SQL Server Create User Based on Login in the Master Database

The most common type is the login user which is stored in the master database. For example, the following shows the command to create a login user under the username – linuxhint:

Start by creating a login named linuxhint.

Next, create a user with the target username using the preivous login.

NOTE: Since we did not switch to a specific database, the previous command stores the user in the master database. If you wish to create the user for another database, switch to the target database.

SQL Server Create User Without Login

You can also create a user which is not mapped to a specific SQL Server LOGIN. For example, the following statements create a user called linuxhint without login:

SQL Server Create a Login User Based on Windows Group

To create a user that uses Windows Group, you can run the following command:

SQL Server Create User for Domain Login

The following example creates a user called linuxhint in the domain called sql_server:

Contained users can only be created in contained databases.

Creating Various Types of Users Using SQL Server Management Studio

If you are just getting started or you do not wish the transact-SQL queries to create users, you can use the SQL Server Management Studio.

This provides a simple and intuitive interface for creating various types of users, including:

1. An SQL user with a login
2. An SQL user without login
3. An SQL user mapped to a given SSL Certificate
4. An SQL user mapped to an asymmetric key
5. A Windows-based SQL User

It also allows the configuration of a plethora of options in a single click including the owned schemas, memberships, etc.

To create a user in a target database using SSMS, open the Object Explore and locate the database in which the user that you wish to create is stored.

Navigate to Security -> Users. Right click and select “New User”.

This should launch a new window that allows you to create various types of users as you see fit. For example, we can create a user with login capabilities as shown in the following:

There you have it! A simple method of creating an SQL Server user using a graphical interface.

Conclusion

In this post, we explored the simple methods of creating various types of users in the SQL Server. We also covered how to use the SSMS to create users.