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.

How to Import MS Access (.mdb) to SQL Database Earlier in How to Export SQL Server Table to Access Database post we learned how to export SQL Server table to Access Database using SQL Server Management Studio Graphical User Interface. Now in this article, we will learn how to import table from MS Access database to […]