Permissions of Fixed Database Roles
Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server 2005. 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: VIEW ANY DATABASE
db_accessadmin
Granted with GRANT option: CONNECT
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 "ms189612.note(en-US,SQL.90).gif")
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
See Also
Reference
Concepts
Permissions of Fixed Server Roles
Permissions Hierarchy
Other Resources
GRANT (Transact-SQL)
DENY (Transact-SQL)
REVOKE (Transact-SQL)