Tag Archives: sql server 2014

SQL Server 2014–Database Roles Explained

The following is a listing of database rolls in SQL Server 2014.  These also apply to SQL Server 2005, SQL Server 2008, SQL Server 2008R2 and SQL Server 2012.

  • db_accessadmin – Group members can add or remove access for logins and groups.
  • db_backupoperator – Group members can back up the database.
  • db_datareader – Group members can read all data from all user tables.
  • db_datawriter – Group members can add, delete, or change data in all user tables.
  • db_ddladmin – Group members can run any data definition language (DDL) command in a database.
  • db_denydatareader – Group members cannot read any data in the user tables within a database.  Deny permissions always take precedence over grant permissions.
  • db_denydatawriter – Group members cannot add, modify, or delete any data in the user tables within a database. Deny permissions always take precedence over grant permissions.
  • db_owner – Group members can perform all configuration and maintenance activities on the database.  db_owner permissions can also drop the database.
    db_securityadmin – Group members can modify role rolls and manage permissions.

All database users belong to the public database role unless specifically denied that permission.  Users will inherit permissions of this role which generally should be restricted.