Rajendra Gupta
Connect to Azure SQL

Database level roles in Azure SQL Database

April 12, 2022 by

This article explores the database-level roles in the Azure SQL Database.

Introduction to Azure SQL Database security management

Database security is critical for an organization to protect unauthorized access to the sensitive and critical data stored in the database objects. There are many layers of security in terms of infrastructure security, database authentication, authorization, encryption.

To manage the SQL Server security, you can authorize permissions for users with specific roles. For example, if a user requires only read access to the database, he should not be given privileged permissions such as db_owner, where he can read, write, create, drop objects. Therefore, it is essential to understand the built-in server and database level roles for providing and managing users’ access seamlessly and effectively.

In the article, Fixed Server roles in Azure SQL Database Server, we covered Azure SQL Database’s built-in fixed server roles.

Once you create a new Azure SQL DB using Azure portal or Azure CLI, the provisioning process deploys a logical Azure SQL Server in the Azure region. You get a virtual master database for managing the configuration and security at the server level. It also configures a server-level principal as a database owner of the Azure database. This account has the highest permissions in Azure SQL DB(PaaS) and has sufficient rights to manage server and database-level security.

The following table summarizes the difference in database security management of Azure SQL Database and on-premises SQL Database.

On-prem SQL Server instance

Azure SQL Database

Manage server-level security

Security folder in SSMS object explorer

Using master database and Azure portal

Windows Authentication

Yes, it supports active directory authentication

It supports Azure active directory authentication.

Server level security role

securityadmin fixed server role

It has a loginmanager database level role that exists in the master database

Supported commands

CREATE\ALTER\DROP LOGIN

CREATE\ALTER\DROP LOGIN (with limited parameters)

Create a new database role(server level)

Dbcreator fixed database role

Database level role – dbmanager

View to check logins

Sys.server_principals

Sys.sql_logins in the master database

View to list all database and their properties

Sys.databases

sys.databases in the master DB

This article will help deep dive into the fixed database roles in Azure DB to manage database-level permissions.

Requirements

To begin with this article, you should understand logins and roles in SQL Server or Azure SQL Database. You require an active Azure SQL Database with administrator access. If you are a beginner in the Azure, you can refer to SQLShack articles using the link – https://www.sqlshack.com/category/azure/

Fixed Database Roles in Azure DB

Expand the Azure SQL DB and navigate to security -> Roles -> Database Roles to get a list of available fixed database roles, expand the Azure SQL DB and navigate to Security -> Roles -> Database Roles. You get the following fixed-database roles.

Fixed Database Roles in Azure DB


The following table lists the database roles and their description.

db_owner

The users in the db_owner fixed database roles provide the highest permissions in a database. Users have the privilege to create, drop, alter, write, or drop the database.

To add a user in the db_owner role, we can use T-SQL stored procedure sp_addrolemember. The following statements provide db_owner permissions to the demologin1.

db_accessadmin

The fixed database role db_accessadmin provides rights to add or remove, create, and manage database users.

The following script provides db_accessadmin role permissions to the demologin1 user.

db_datareader

The db_datareader role grants rights required to read data from all tables and views in the database. For example, suppose you want developers to read data from the production database. In this case, you can provide him db_datareader role in the respective database.

The following script provides db_ datareader role permissions to the demologin1 user.

db_datawriter

The db_datareader role grants rights required to write(insert, update) data from all tables and views in the database. For example, you can add your application account to this role to perform data inserts, updates.

The following script provides db_datawriter role permissions to the demologin1 user.

db_ddladmin

The db_ddladmin fixed database role grants permissions to create and manage database objects. For example, you can add users in this role to create, alter or drop the objects in the database.

The following script provides db_ddladmin role permissions to the demologin1 user.

db_denydatareader

The db_denydatareader role denies access for a user to read data from any table or view in the database using the db_denydatareader role.

The following script provides db_denydatareader role permissions to the demologin1 user.

db_denydatawriter

The db_denydatawrite database role denies a user to write data into the table or view of the database.

The following script provides db_denydatawriter role permissions to the demologin1 user.

db_backupoperator

The db_backupoperator provides permissions to back up the SQL database. If you execute the T-SQL statement to provide db_backupoperator role in Azure DB, it executes successfully.

The db_backupoperator role

However, the MS documentation states db_backupoperator is not applicable in the Azure SQL database because you cannot run the BACKUP DATABASE statement in the Azure DB.

Public

Once you create a new database user and do not provide server or database level permissions, it belongs to the public database role. You cannot remove a user from the public role. If you try to access an object using the public role, the user inherits permissions granted to the public role.

Additional roles in the virtual master database

If you look at the same database roles in the virtual master database, you get additional database roles, as shown below.

Additional roles in the virtual master database

Azure Database contains additional security roles: loginmanager for creating logins and dbmanager for creating databases.

Note: The users in the master database can only be added to these database roles.

Loginmanager role

Users in the loginmanager database role can create and delete the logins in the master database.

dbmanager role

The dbmanager role allows the user to create a database, delete a database as a database owner. It allows users to connect the Azure database as a DBO user that contains all DB permissions. The user in the role does not have permission to access other databases that they do not own. Let’s explore these particular database roles in the virtual master database with examples.

  • Create a new login named [Login1] in the master database.
  • Create a new user [login1User] from the login
  • Add the user in the loginmanager fixed database role
  • Verify the user and its assigned database roles using the following query. The query uses the system views sys.database_role_members and sys.database_principals to get the required data in Azure DB.

As shown below, [login1user] is a member of the loginmanager role.

Check effective permission

  • Connect to Azure SQL Database using SQL login [login1]

Connect to Azure SQL

As stated above, the logins in the loginmanager role can create or delete the logins in the master database. Let’s try creating a new login and dropping it as well.

  • Create a login

Create a login

  • Drop a login

Drop a login

However, if you try to create a new table, you get the permission denied error. In this case, you can add database roles such as db_owner or db_ddladmin to create database objects.

Create table

We can add users in the multiple database roles as well. For example, we can add the user in the dbmanager fixed database role as well. Now, this user can create or drop additional Azure SQL Databases.

Now, rerun the select statement to list all database-principals who are members of a database-level role. You can see the [login1user] is a member of both roles – dbmanager and loginmanager.

Check role memberships

You should reconnect to the Azure database using login [login1], disconnect it and reconnect for effective permissions.

Note: Always refer to Microsoft documentation for more details on this topic.

Conclusion

This article presented an overview of database-level roles in the Azure SQL Database. The virtual master database contains additional roles – dbmanager and loginmanager for managing the permissions effectively. You should manage database permissions to prevent any unauthorized access to the database.

Rajendra Gupta
7,285 Views