SQL Server 2008 Fixed Database-level Roles

SQL Server 2008 Fixed Database-level Roles

Several fixed database-level roles exist in each SQL Server database. Thosepredefined roles are used to grant a predefined set of permissions to database users and, with the exception of the public role, they are not used to assign permissions to individual objects.

db_accessadmin Granted: ALTER ANY USER, CREATE SCHEMA
db_backupoperator Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT
db_datareader Granted: SELECT
db_datawriter Granted: DELETE, INSERT, UPDATE
db_ddladmin Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES
db_denydatareader Denied: SELECT
db_denydatawriter Denied: DELETE, INSERT, UPDATE
db_owner Granted with GRANT option: CONTROL
db_securityadmin Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION
public Granted: SELECT on system views

All database users belong to the public database role by default. It is a best practice to avoid using the public database role when assigning permissions.