SQL Server Fixed Database Roles Cheat Sheet Print

Overview

This cheat sheet covers the built-in fixed database roles in SQL Server, when to use each one, and T-SQL commands for managing role membership.

db_owner

• Full control of the database including creating, altering, and dropping objects, managing permissions, and backup/restore
• Equivalent to being a database administrator
• Use when: You want someone to manage everything within a single database

db_datareader

• Can read all tables and views in the database
• Cannot modify data
• Use when: You need read-only or reporting access

db_datawriter

• Can insert, update, and delete data in all tables and views
• Cannot create or drop schema objects
• Use when: You need someone to edit content but not change the database structure

db_ddladmin

• Can run any DDL (Data Definition Language) commands including create, alter, and drop for tables, views, and stored procedures
• Cannot manage permissions or perform backup/restore
• Use when: A developer needs to change schema but should not have full control

db_securityadmin

• Can manage role membership and permissions inside the database
• Use with caution - this role could allow someone to escalate themselves or others to higher roles
• Use when: You want someone to manage who can do what inside a specific database

db_accessadmin

• Can add and remove users for the database
• No other permissions by default
• Use when: A team lead should control who has access without full DBA rights

db_backupoperator

• Can run BACKUP commands for the database
• Cannot restore
• Use when: You want delegated backup responsibility without granting full control

db_denydatareader / db_denydatawriter

• Explicitly denies read or write permissions
• Overrides all other roles
• Use when: You need to enforce a hard "no read" or "no write" rule for a specific user

Quick Reference

• Full DB admin: db_owner
• Developers: db_ddladmin + db_datareader / db_datawriter
• Reporting: db_datareader
• Content editors: db_datawriter
• Access management: db_accessadmin or db_securityadmin

T-SQL Commands for Role Management

Add a login to a database and assign a role:

USE MyDatabase;
CREATE USER [MyUser] FOR LOGIN [MyLogin];
EXEC sp_addrolemember 'db_owner', 'MyUser';

Add a user to another role (example: db_datareader):

USE MyDatabase;
EXEC sp_addrolemember 'db_datareader', 'MyUser';

Remove a user from a role:

USE MyDatabase;
EXEC sp_droprolemember 'db_datareader', 'MyUser';

View all roles for a user:

USE MyDatabase;
EXEC sp_helprolemember;

Notes

• Always follow the principle of least privilege - assign only the roles required for the task
• Be particularly cautious with db_securityadmin and db_owner as these roles carry significant risk if misassigned
• db_denydatareader and db_denydatawriter will override any other roles that grant read or write access