Viewing Roles and Permissions Granted to Users of Azure SQL Server Instance

1. Introduction

Roles and permissions are essential to securing Microsoft Azure SQL Server databases. Providing users with the appropriate level of access is crucial for compliance and security as businesses grow.

To query and examine the server-level, database-level, and custom roles that Azure SQL Database offers, we can use the Azure Portal, the Azure Active Directory integration, or custom T-SQL statements.

In this tutorial, we’ll present the different types of roles, how to view user permissions, and the best practices for auditing access in Azure SQL Server.

2. Understanding the Roles in Azure SQL Server

Roles are predefined or user-defined security groupings. We can assign users to roles rather than giving them permissions on a case-by-case basis.

We differentiate between server-level and database-level roles:

Additionally, organizations can establish custom roles to comply with the least privilege principle. With user-defined custom roles, administrators can customize permissions to meet particular job functions or operational requirements.

This ensures that users only have access to the resources they need for their tasks, reducing the possibility of unintentional or malicious data exposure.

3. Methods for Viewing Roles Using a Query

Azure SQL supports querying system views to retrieve roles and permissions.

3.1. View Database Roles Assigned to Users

Here’s an SQL query that retrieves a list of database roles and their members from the current SQL Server database:

SELECT dp.name AS DatabaseRole,
mp.name AS MemberName,
mp.type_desc AS MemberType
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id
ORDER BY dp.name;

The sys.database_role_members view maps database roles to their members, showing which users belong to which roles. The sys.database_role_principals view contains information about all database-level security principals, including users, roles, and application roles.

Here’s what the output typically looks like:

The actual output depends on our database’s current role assignments. We’ll see all users and roles that are members of other roles, sorted by the role name.

This query is a great way to get a clear snapshot of role memberships. It’s particularly useful when auditing permissions and managing access.

3.2. View Server-Level Roles

Getting server-level role memberships in SQL Server is useful for auditing administrative privileges and understanding access control at the instance level.

Here’s an SQL query that retrieves a list of server roles and their members:

SELECT spr.name AS ServerRole,
spm.name AS MemberName
FROM sys.server_role_members srm
JOIN sys.server_principals spr ON srm.role_principal_id = spr.principal_id
JOIN sys.server_principals spm ON srm.member_principal_id = spm.principal_id
ORDER BY spr.name;

The sys.server_role_members view shows the relationships between server-level roles and their members. The sys.server_principals view lists all server-level security principals, such as logins and server roles, providing metadata about each.

Here’s what the output typically looks like:

The actual output will reflect the logins configured on the SQL Server instance and their assigned server roles. These roles govern various capabilities, such as managing logins, configuring server settings, or executing high-level tasks.

3.3. View Explicit Permissions Granted to a User

In contrast to the query showing role memberships, we’ll now focus on explicit permissions granted directly to users or roles.

When auditing who has access to what, and under whose authority, a detailed view of explicit database-level permissions comes in handy:

SELECT dp1.name AS Grantee,
dp2.name AS Grantor,
perm.permission_name,
perm.state_desc,
obj.name AS ObjectName
FROM sys.database_permissions perm
JOIN sys.database_principals dp1 ON perm.grantee_principal_id = dp1.principal_id
JOIN sys.database_principals dp2 ON perm.grantor_principal_id = dp2.principal_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
ORDER BY dp1.name, perm.permission_name;

This query serves as an excellent tool to demonstrate granular access control within relational databases.

The following table provide details about the output columns in the query.

Here’s how the output typically looks like:

Furthermore, this highlight security best practices in enterprise data environments, and clarify the distinctions between role based vs explicit permission assignments.

4. Viewing Roles Using the Azure Portal

The Azure Portal provides a graphical interface for viewing and managing user roles:

The steps to get there are:

• We’ll navigate to the Azure Portal and open the SQL databases section
• Select the target database, then configure the server firewall and set the Active Directory admin for identity mapping
• Within the database view, we’ll access the Access control (IAM) panel to review user roles and permissions.

If the SQL Server is integrated with Azure AD, permissions can also be reviewed through the Azure Active Directory portal. Further, we’ll navigate to enterprise applications, then select users and groups to audit group-based assignments across multiple users.

This is especially useful when auditing group-based assignments across multiple users.

5. Best Practices for Managing and Reviewing Permissions

To ensure secure and scalable access management, it’s essential to follow the principle of least privilege. This is done by assigning only the permissions that are strictly necessary.

For routine audits, we need to use Azure Automation or SQL queries. As a result of these audits, we can detect excessive or out-of-date permissions, ensure adherence to internal policies, and identify potential security threats before they become a problem.

We should also implement role-based access control through Azure AD groups for better scalability, and enable logging via Azure Monitor and SQL Auditing to effectively track access changes.

6. Conclusion

In this article, we discussed about how Azure SQL Server roles and permissions ensures secure and legal database access. T-SQL queries, the Azure Portal, and Azure AD help administrators keep track of who has access and what they can do with it. 

The ability to manage roles and permissions in Azure SQL Server is essential for preserving data security, operational integrity, and regulatory compliance.

When onboarding new users, distributing access among teams, or getting ready for an audit, knowing who can do what and why is crucial.