Share via


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

ms189612.note(en-US,SQL.90).gifImportant:

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

Database-Level Roles

Concepts

Permissions of Fixed Server Roles
Permissions Hierarchy

Other Resources

GRANT (Transact-SQL)
DENY (Transact-SQL)
REVOKE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance