At Access control of MS SQL – Part 2, we tell about how to assign Logins with Server Roles. In this post, we will take about “Database Roles”

In SQL Server, there are 9 fixed server roles. Each of them are assigned some unchangeable permission.
1. db_owner can do anything within the database.
2. db_securityadmin can manage “Users” and assign database level permission.
3. db_accessadmin can manage “Logins” to database’s connection.
4. db_backupoperator can backup the database.
5. db_ddladmin can run any Data Definition Language (DDL).
6. db_datawriter can add, delete or modify data in tables.
7. db_datareader can read data in tables.
8. db_denydatawriter cannot add, delete or modify data in tables.
9. db_denydatareader cannot read data in tables.

How to assign Fixed Database roles to “Users” at “Users” via GUI:
1. Expand suitable database > Expand “Security” > Expand “Users” > Right-click suitable user > select “Properties”
2. Select “Membership” > click suitable database roles > click “OK”

How to assign Fixed Database roles to “Users” at “Database Roles” via GUI:
1. Expand “Security” > Expand “Roles” > Expand “Database Roles” > Right-click suitable database role > select “Properties”
2. Click “Add” to add “Users” to this database roles

How to assign Fixed Database roles to “Users” via GUI:

-- Change available database to AdventureWorks2016
USE AdventureWorks2016;
-- Add user "sql-operator" to "db_owner"
-- P.S.: sp_addrolement is "database role", "users"
EXEC sp_addrolemember 'db_owner', 'sql_account'

-- Drop user "sql-operator" from "db_owner"
-- P.S.: sp_addrolement is "database role", "users"
EXEC sp_droprolemember 'db_owner', 'sql_account'