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.
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.
|
1 2 3 |
EXEC sp_addrolemember 'db_owner', '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.
|
1 2 3 |
EXEC sp_addrolemember 'db_accessadmin', 'demologin1'; |
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.
|
1 2 3 |
EXEC sp_addrolemember 'db_datareader', 'demologin1'; |
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.
|
1 2 3 |
EXEC sp_addrolemember 'db_datawriter', 'demologin1'; |
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.
|
1 2 3 |
EXEC sp_addrolemember 'db_ddladmin', 'demologin1'; |
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.
|
1 2 3 |
EXEC sp_addrolemember 'db_denydatareader', 'demologin1'; |
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.
|
1 2 3 |
EXEC sp_addrolemember 'db_denydatawriter', 'demologin1' |
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.
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.
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.
|
1 2 3 |
CREATE LOGIN login1 WITH password='P@ssw0rd123'; |
- Create a new user [login1User] from the login
|
1 2 3 |
CREATE USER login1User FROM LOGIN login1; |
- Add the user in the loginmanager fixed database role
|
1 2 3 |
EXEC sp_addrolemember 'loginmanager', 'login1User'; |
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT roles.principal_id AS RolePrincipalID , roles.name AS RolePrincipalName , database_role_members.member_principal_id AS MemberPrincipalID , members.name AS MemberPrincipalName FROM sys.database_role_members AS database_role_members JOIN sys.database_principals AS roles ON database_role_members.role_principal_id = roles.principal_id JOIN sys.database_principals AS members ON database_role_members.member_principal_id = members.principal_id; GO |
As shown below, [login1user] is a member of the loginmanager role.
- Connect to Azure SQL Database using SQL login [login1]
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
- 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.
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.
|
1 2 3 |
EXEC sp_addrolemember 'dbmanager', 'login1User'; |
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023