How To Create Database User In SQL Server
In the previous article “How To Create SQL Server Login“, we just seen how to create SQL Server Login Using SSMS GUI (Graphical User Interface) but didn’t created any user and does not assigned this login to any user.
Here we will see how to create new database user and allow permissions and rights to perform certain tasks. Let’s start…
Step-I: Open SQL Server Management Studio and click on Connect button.

Step-II: Expand Database (HR in this demo) for which you want to create user and then expand Security tab by clicking on +icon as below.

Step-III: Right click on Users and click on New User… as shown below.

Step-IV: Select User type
Here are five types of User types
- SQL user with login
- SQL User without login
- User mapped to a certificate
- User mapped to an asymmetric key
- Windows user
In this demo we will select SQL user with login
Provide the user name which you want to use.
Provide Login name, and
Provide Default Schema

Step-V: You can assign membership to user at the time of user creation by clicking on the Membership tab or by right clicking on the user name in the Security tab as follows.

Step-VI: A new window will appear as below. Now assign the membership which you want to assign. In this demo I am going to assign db_onwer membership. To allow the membership
- Click on Membership checkbox from the Select a page tab.
- Check db_owner checkbox
- Click on OK button.

Since we have created a database user DB_User and assigned db_owner schema as well as membership db_owner, so we can do all kind of work including tables, stored procedures, views, triggers etc.
When we create object with the above login and user, all objects will be created with the default schema db_owner.
This user can work only with the database in which you have created. Since, we have created a new user DB_User in HR database so we can only work with this HR database.
Recommended

How To Change Schema Name in SQL Server

What is The Public Server Role in SQL Server

How To Create SQL Server Login

How To Generate SQL Database Script With Data

How to Import MS Access (.mdb) to SQL Database

How to Export SQL Server Table To Access Database

How to Import Data From Excel (XLSX) to SQL Table

How to Export SQL Table Data to Excel (XLSX)
