Permissions of Fixed Database Roles (Database Engine)
Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server. Fixed database roles are provided for convenience and backward compatibility. Assign more specific permissions whenever possible.
The following table describes the mapping of the fixed database roles to permissions.
Fixed database role |
Database-level permission |
Server-level permission |
|---|---|---|
db_accessadmin |
Granted: ALTER ANY USER, CREATE SCHEMA Granted with GRANT option: CONNECT |
Granted: VIEW ANY DATABASE |
db_backupoperator |
Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT |
Granted: VIEW ANY DATABASE |
db_datareader |
Granted: SELECT |
Granted: VIEW ANY DATABASE |
db_datawriter |
Granted: DELETE, INSERT, UPDATE |
Granted: VIEW ANY DATABASE |
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 |
Granted: VIEW ANY DATABASE |
db_denydatareader |
Denied: SELECT |
Granted: VIEW ANY DATABASE |
db_denydatawriter |
Denied: DELETE, INSERT, UPDATE |
|
db_owner |
Granted with GRANT option: CONTROL |
Granted: VIEW ANY DATABASE |
db_securityadmin |
Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION |
Granted: VIEW ANY DATABASE |
dbm_monitor |
Granted: VIEW most recent status in Database Mirroring Monitor .gif "Important note") Important
The dbm_monitor fixed database role is created in the msdb database when the first database is registered in Database Mirroring Monitor. The new dbm_monitor role has no members until a system administrator assigns users to the role.
|
Granted: VIEW ANY DATABASE |
Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role. Members of the db_owner fixed database role are identified as the dbo user in the databases, but users with the CONTROL DATABASE permission, are not.