Understanding SQL Roles

Creating SQL Roles

What are SQL Roles – Predefined collection of objects and permissions?

Roles allow the dba to manage permissions more efficiently

We first create roles and then assign permissions to roles, and then add logins to the roles

SQL Server supports four types of roles

  • Fixed database roles – These roles already have pre – defined set of permissions
  • User-defined database roles – These roles have their set of permissions defined by the sa
  • Fixed server roles – These roles already have pre – defined set of permissions
  • User-defined server roles – These roles have their set of permissions defined by the sa

The following shows the fixed database-level roles and their capabilities. These roles exist in all databases.

Database-level role

db_owner      

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

db_securityadmin    

Members of the db_securityadmin fixed database role can modify role membership and manage permissions (be careful)

db_accessadmin       

Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator 

Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter          

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader          

Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter  

Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader 

Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.