Creating SQL Server 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.
Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
Members of the db_securityadmin fixed database role can modify role membership and manage permissions (be careful)
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.
Members of the db_backupoperator fixed database role can back up the database.
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
Members of the db_datareader fixed database role can read all data from all user tables.
Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.