Fixed Database Roles in SQL Server

In earlier article we have seen Fixed Server Roles, today we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.

EXEC sp_helpdbfixedrole
GO
  1. db_owner
  2. db_accessadmin
  3. db_securityadmin
  4. db_ddladmin
  5. db_backupoperator
  6. db_datareader
  7. db_datawriter
  8. db_denydatareader
  9. db_denydatawriter

You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles

Fixed Database Roles

1. db_owner

All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.

2. db_accessadmin

As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.

3. db_securityadmin

Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.

4. db_ddladmin

If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.

5. db_backupoperator

db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.

6. db_datareader

If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.

7. db_datawriter

All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.

8. db_denydatareader

As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.

9. db_denydatawriter

This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.

 

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

1 Response

  1. Mohammed Qadeer says:

    Hi Mr. Manzoor,

    Thanks for uploading the valuable information regarding Database Roles.
    I need your little more help…

    For a normal SQL USER (SQL authentication) what are the minimum permission/roles etc required to take the backup of SQL Server Database.
    Apart from “db_backupoperator” what all sufficient permission is required?

    Please reply…

    Regards
    Mohammed Qadeer

Leave a Reply

Your email address will not be published.