SQL server security

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.



Fig a.

 Click on the Security Node then  right click on login noe to add new Login as shown in fig b.


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.

Fig. c.

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


Fig. d.


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





Fig. e.

Click Ok to have Connect and Select permission to Adventureworks for user Seed .

Step 5.
   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.

Thanks, Satalaj.

By Sat Sat   Popularity  (1321 Views)