Database roles provide the assignment of a set of database-specific permissions to an individual or a group of users. Database roles can be assigned to Windows Authenticated logins or SQL Server Authenticated logins. Roles that are assigned to Windows Authenticated logins can be assigned to NT users and NT groups. Roles can also be built with a hierarchical model.
Database roles are database specific. SQL Server provides three types of roles:
- Predefined database roles
- User-defined database roles
- Implicit roles
Predefined Database Roles
Predefined database roles are standard SQL Server database roles. Each database in SQL Server has these roles. Predefined database roles make it easy to delegate responsibility.
Predefined database roles are database specific and cannot be customized. The table below provides a listing and explanation for each type of database role.
| db_owner |
Has complete access to all objects within the database, can drop and re-create objects, and has the capability to assign object permissions to other users. It can modify database settings and perform database maintenance tasks. This role encompasses all functionality listed in the other predefined database roles.
|
| db_accessadmin |
Controls access to the database by adding or removing Windows Authentication users and SQL Server users.
|
| db_datareader |
Has complete access to SELECT data from any table in the database. This role does not grant INSERT, DELETE, or UPDATE permissions on any table in the database.
|
| db_datawriter |
Can perform INSERT, DELETE, or UDPATE statements on any table in the database. This role does not grant SELECT permission on any table in the database
|
| db_ddladmin |
Has the capability to create, modify, and drop objects in the database.
|
| db_securityadmin |
Performs security management within the database. This role manages statement and object permissions and roles within the database.
|
| db_backupoperator |
Has the capability to back up the database.
|
| db_denydatareader |
Denies SELECT permission on all tables in the database. However, this role does allow users to modify existing table schemas. It does not allow them to create or drop existing tables.
|
| db_denydatawriter |
Denies data modification statements (INSERT, DELETE, or UPDATE) from being performed against any tables in the databases
|
| public |
Every database user is a member of the public role. A user automatically becomes part of the public role when she is permitted access to the database.
|