1. db_owner
• Full control of the database (create/alter/drop objects, manage permissions, backup/restore, etc.).
• Equivalent to being a “database admin.”
• Use when: You want someone to manage everything in just one DB.
—————
2. db_datareader
• Can read all tables and views in the database.
• Cannot modify data.
• Use when: You need read-only/reporting access.
—————
3. db_datawriter
• Can insert, update, delete data in all tables and views.
• Cannot create or drop schema objects.
• Use when: You need someone to edit content but not change structure.
—————
4. db_ddladmin
• Can run any DDL (Data Definition Language) commands: create/alter/drop tables, views, stored procs, etc.
• Cannot manage permissions or backup/restore.
• Use when: A developer needs to change schema but shouldn’t control everything.
—————
5. db_securityadmin
• Can manage role membership and permissions inside the database.
• Use with caution — they could escalate themselves or others to higher roles.
• Use when: You want someone to manage who can do what inside that DB.
—————
6. db_accessadmin
• Can add/remove users for the database.
• No other permissions by default.
• Use when: A team lead should control who has access, without full DBA rights.
—————
7. db_backupoperator
• Can run BACKUP commands for the database.
• Cannot restore.
• Use when: You want delegated backup responsibility without full control.
—————
8. db_denydatareader / db_denydatawriter
• Explicitly deny read or write permissions.
• Overrides other roles.
• Use when: You want to enforce “no read” or “no write” rules for a specific user.
—————
✅ Quick Takeaway
• For full DB admin → db_owner.
• For developers → db_ddladmin + db_datareader/db_datawriter.
• For reporting → db_datareader.
• For content editors → db_datawriter.
• For access management → db_accessadmin or db_securityadmin.
—————
📝 T-SQL Commands for Role Management
Add a login to a database and assign role
USE MyDatabase;
CREATE USER [MyUser] FOR LOGIN [MyLogin];
EXEC sp_addrolemember 'db_owner', 'MyUser';
Add to another role (example: db_datareader)
USE MyDatabase;
EXEC sp_addrolemember 'db_datareader', 'MyUser';
Remove from a role
USE MyDatabase;
EXEC sp_droprolemember 'db_datareader', 'MyUser';
View all roles for a user
USE MyDatabase;
EXEC sp_helprolemember;
—————