Here we learn how to create SQL Database, how to create database user and set permission, user mapping with newly created 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
Once we login as Admin, We can create database it in two ways. First using SQL Server Management Studio and then Using Script
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.
Now we have to create new user with login details for our newly created database
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.
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 and new User Creation Done!
Now disconnect from admin login, and login with new credential into new database, enjoy