As a follow-up to my previous article on listing SQL Server read/write permissions, I received a comment requesting a similar guide for Azure SQL Database. In this article, I will provide an overview of Azure SQL Database permissions and explain the key differences compared to a traditional SQL Server environment.

When working with Azure SQL Database, there are a few important considerations to keep in mind. First, when creating a database in Azure, you must also create a server. During this process, you will specify an administrator user and password. This administrator user will have permissions in the [master] database and will be able to create other server logins.

Unlike SQL Server, Azure SQL Database does not have a table named [sys].[server_permissions] or [sys].[server_role_members]. Instead, you can view the server logins by querying [sys].[sql_logins]. Additionally, the administrator user in Azure SQL Database does not have any server role and cannot assign roles to new users.

Furthermore, the administrator user in Azure SQL Database is limited in terms of assigning permissions in the [master] database. They can only assign [dbmanager] and [loginmanager] roles, and cannot create schemas or roles in the [master] database. Additionally, the administrator user cannot assign permissions to other users on INFORMATION_SCHEMA or SYS schemas in the [master] database.

For each individual database in Azure SQL Database, including [master], you need to create database logins from server logins. You can view the database logins by querying [sys].[sysusers] where [islogin]=1. However, it’s important to note that you cannot include the “USE [database]” clause in your statements when querying [sys].[databases]. Therefore, the only way to aggregate the results is by connecting individually to each database, which can be done programmatically using PowerShell.

When it comes to permissions within the individual databases, Azure SQL Database has a table named [sys].[database_permissions]. However, there are fewer types of permissions compared to SQL Server. The original script provided in the example article includes these permissions, but some of them may not be supported in future versions of Azure SQL Database.

Here is a sample query that you can run in the [master] database and then in each individual database to list the permissions:

;WITH [explicit] AS (
   SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date],
         [dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],
         CAST('' AS SYSNAME) [grant_through]
   FROM [sys].[database_permissions] [dbp]
   INNER JOIN [sys].[database_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]
   WHERE ([dbp].[type] IN ('IN','UP','DL','CL','DABO','IM','SL','TO') OR [dbp].[type] LIKE 'AL%' OR [dbp].[type] LIKE 'CR%')
     AND [dbp].[state] IN ('G','W')
   UNION ALL
   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]
   FROM [sys].[database_principals] [dp]
   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
   INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]
   ),
[fixed] AS (
   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[name] [permission], CAST('' AS SYSNAME) [grant_through]
   FROM [sys].[database_principals] [dp]
   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
   INNER JOIN [sys].[database_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]
   WHERE [p].[name] IN ('db_owner','db_datareader','db_datawriter','db_ddladmin','db_securityadmin','db_accessadmin','dbmanager','loginmanager')
   UNION ALL
   SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]
   FROM [sys].[database_principals] [dp]
   INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
   INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]
   )
SELECT DISTINCT DB_NAME() [database], [name] [username], [type_desc], [create_date], [permission], [grant_through]
  FROM [explicit]
 WHERE [type_desc] NOT IN ('DATABASE_ROLE')
UNION ALL
SELECT DISTINCT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]
  FROM [fixed]
 WHERE [type_desc] NOT IN ('DATABASE_ROLE')
ORDER BY 1, 2
OPTION(MAXRECURSION 10);

By running this query, you will get a list of permissions for each database, including the database name, username, type, create date, permission, and grant through information.

Understanding Azure SQL Database permissions is crucial for managing access and security within your database environment. By following the guidelines and querying the appropriate system tables, you can effectively manage user permissions and ensure the integrity of your data.

Thank you for reading this article. I hope you found it helpful in understanding Azure SQL Database permissions. If you have any questions or comments, please feel free to leave them below.