How to Create SQL database user with password

Here we learn how to create sql database , and create sql database user with password and set permission, user mapping with newly created MS SQL database.

First we need to login to SQL server as Administration, as a admin user should have required right to create a new Database and User

sql database login

Once we login as Admin, We can create database it in two ways. First using SQL Server Management Studio and then Using Script

Create SQL Database Step by Step

Step 1:
Right click on Database on Solution Explorer and Click “New Database”, in open dialog set the new database name you want, (for example here we have given “WTRDatabase”) then click OK.

create sql database

Now we have to create new user with login details for our newly created database

Create SQL Server User Login

Step 2:
Right click on “Logins” => “Security” , then select “New Login”, in open dialog set login name (refer the picture below), now instead of “Windows Authentication” , select “SQL Server Authentication” , set the newly created database as “Default Database”, then click OK.

create sql server user

Step 3:
Set “Server Roles”, Under “Logins”, right click on newly created login name
Select “Server Roles” and gives the required roles to user (like dbcreator, setupadmin).

Then click on "User Mapping", select the database you want to map with this user, and select checkbox of “Database role membership” (refer the picture below)

sql database user mapping

SQL Database and new User Creation Done!
Now disconnect from admin login, and login with new credential into new database, enjoy

You can also write query to create a new database, make sure you login as administrator!

CREATE DATABASE [testdb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'testdb', FILENAME = N'D:\TestDB\testdb.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'testdb_log', FILENAME = N'D:\TestDB\testdb_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO

Change the ldf and mdf file location as per your machine drive and folder name!

 
Hire SQL Developer
Create SQL database user with password and set Permission
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
MS SQL Examples | Join MS SQL Course