One way SQL server to easily manage access is using database roles. SQL server has several built-in database roles.
db_owner role member by default can perform any action within the database. However, except the actual database owner (user name dbo), other members in the db_owner role can be deny access by placing them in other database roles with deny access to objects, such as db_denydatareader.
Other roles functions: db_backupoperator: perform database back db_datareader: can read data db_datawriter: can INSERT, UPDATE and DELETE data db_ddladmin: can create/modify any objects but can read/write data db_denydatareader: can’t read data db_denydatawriter: can’t write data db_securityadmin: can grant access to user db_accessadmin: can create user but can’t grant access to objects and data
In Azure SQL database, two additional roles are introduced in master db: dbmanager: can create database. Equivalent to dbcreator server role in on-premises SQL server loginmanager: can create logins. Equivalent to securityadmin server role in on-premises SQL Server
Apart the above database roles, there are fixed server roles in both on-premises SQL Server and Azure SQL Managed Instance: sysadmin: can perform any activity on the server serveradmin: can change server-wide configuration settings and can shut down the server securityadmin: can manage logins and grant/revoke server and database level permissions processadmin: can kill processes running inside of SQL Server setupadmin: can add and remove linked server using T-SQL bulkadmin: can run BULK INSERT T-SQL statement diskadmin: can manage backup devices in SQL Server dbcreator: can create/restore/alter/drop any database
public: every sQL login belongs to this role. Unlike the above fixed server role permissions can’t be changed, permission can be granted/denied/removed from public role