SQL server security
By Sat Sat
In this article we are going to see how to create new user in MS SQL server and give him appropriate privileges to database. Intention of this article is to let the beginners know how simple it is to be done. Though it is simple most of the organization still uses Sa as user name while connecting to MS SQL SERVER DB.
In this article we will be creating only one user which will be having following roles
1. Connect to Database and only view the database to whom he has rights. (Adventure works DB)
2. Fire only Select statements (Read).
Let s follow below steps.
Step 1. Connect to MS SQL
Connect o MS SQL server by using Credentials of the user which is having appropriate
privileges like add new user, create database, control the database etc.
After getting connected you will see hierarchy as shown in Fig a.
Click on the Security Node then right click on login noe to add new Login as shown in fig b.
Step 2. Create New User
Now we will create new user as Seed and password as Passw@rd in our Master data base .
as shown in fig. C . select SQL server Authentication and Uncheck Enforce password polices.
Click ok to continue here we have successfully created new user as Seed.
In left pen under Login node you will see newly created user Seed.
Double click to open his property again, you will see fig C.
Step 3. User Mapping.
Let us map this user to Database using User Mapping as shown in fig d.
Here I have mapped the user to Database named Adventureworks
Click Ok to continue
Step 4. Give permission to user
Click on Database right click on the database to whom we have assigned Seed user.
Here in our case it is AdventureWorks DB. Click on Adventure works Properties then select permission page as shown in fig e.
See Section “Explicit permission for Seed “
1. Grant Connect using Seed
2. And Grant Select to Seed user
Click Ok to have Connect and Select permission to Adventureworks for user Seed .
Open new instance of MS SQL server and try to connect using user Seed and password as Passw@rd to MS SQL server and perform destructive testing.
Popularity (1157 Views)