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.