Database Lelel Roles in SQL Server

Database Roles :

Role is used to group a set of privileges .

We can reduce the process of granting and taking back permissions to large no of users with help of roles.

SS supports 3 types of roles .

                 1. Fixed Database Roles .

                 2. Custom Database Roles.

                 3. Application Database Roles.

1. Fixed Database Roles :

There are 8 fixed database roles which comes along with SS database .

SS supports the following fixed db roles .

1. db_owner :

  Member of db_owner role can perform any task on the db.

2. db_ddladmin :

  Member of ddl_admin can work with DDL commands such as create , alter and drop .

3. db_securityadmin:

  Member of db_securityadmin can create users ,roles , schemas , certificates etc .

4. db_datareader :

  Member of db_datareader can read data from any object.

5. db_datawriter :

  Member of db_datawriter can work with insert , update and delete commands.

6. db_denydatareader :

  Member of db_denydatareador cannot work with any select command.

  No other users of db can grant the permission .

7.  db_accessadmin :

  Member of db_accessadmin can work with disk files.

8.  db_backupoperator :

  Member of db_backupoperator role can take backup of db and can restore db.


2. Custom Database Roles :

We can create role with required privileges .

steps to create custom database roles .

1. Creating Role

 Syn : Create Role <Role_Name>

2. Granting Permissions to the Role

Syn : Grant .... to  <Role Name>

3. Adding user to the role using sp_addrolemember


Requirement:

Create a role with the name customercare _role  Grant select , insert on emp table and also Grant Select on Library Schema and also Grant backup database permission .

Now add the user John to the above role .

Steps to achieve the above requirement

1. Creating the Role

        user test
           Go
create role CusomerCare_Role

2. Granting privileges to the role

         Use Test
            Go
Grant Select , Insert on Emp to CustomerCare_Role
Go
Grant Select on ::[Library] to CustomerCare_Role
Go
Grant Backup database to CustomerCare_Role
Go

3. Adding John to the Role

        Use Test
             Go
sp_addrolemember @rolename='CustomerCare_Role' , @member name='John'