I wrote an article to List SQL Server read/write permissions, and a user commented it would be nice to get something similar about Azure SQL Database. The query is basically the same as for a single database, but there are several things you need to be aware of:
- In Azure, when you create the database you must create a server, there you specify the administrator user and password. This will be a server login with permissions in the [master] database which is able to create other server logins. You can view the server logins querying [sys].[sql_logins], there is no table named [sys].[server_permissions].
- In the table [sys].[server_principals] you won't find the server logins. The table [sys].[server_role_members] is empty.
- The administrator user doesn't have any server role, and can't assign it to new users.
- The administrator user can't assign any [db_*] database role in the [master] database, only [dbmanager] and [loginmanager].
- The administrator user can't create schemas or roles in the [master] database.
- The administrator user can't assign permissions to other users (or itself) on INFORMATION_SCHEMA or SYS schemas in the [master] database.
- For each database, including [master], you need to create database logins from server logins. You can view the database logins querying [sys].[sysusers] where [islogin]=1.
- You can query [sys].[databases] and it contains a list of all of them, but you can't include the "USE [database]" clause in your statements, so the only way to aggregate the results is by connecting individually to each database; this can be done programmatically i.e. using PowerShell.
- There are fewer types in [sys].[database_permissions], but I've left them from the original script in case some of them are supported in future versions.
Here is the query, which you can run first in the [master] database and then in each individual database:
;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);
Here is a sample of the output from the master database, note the administrator user is "pabechevb" and those permissions were already assigned. I manually granted "dbmanager" and "loginmanager" to the "readonlyuser":
database username type_desc create_date permission grant_through master dbo SQL_USER 2003-04-08 09:10:42.287 db_owner master pabechevb SQL_USER 2021-04-30 13:09:20.290 ALTER master pabechevb SQL_USER 2021-04-30 13:09:20.290 ALTER ANY USER master pabechevb SQL_USER 2021-04-30 13:09:20.290 SELECT master readonlyuser SQL_USER 2021-04-30 14:29:21.700 dbmanager master readonlyuser SQL_USER 2021-04-30 14:29:21.700 loginmanager master readonlyuser SQL_USER 2021-04-30 14:29:21.700 SELECT loginmanager
And here is a sample of the output from an individual database after the "readonlyuser" is granted all permissions:
database username type_desc create_date permission grant_through pabechevb dbo SQL_USER 2003-04-08 09:10:42.287 db_owner pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 ALTER pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 CONTROL pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 DELETE pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 INSERT pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 SELECT pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 TAKE OWNERSHIP pabechevb readonlyuser SQL_USER 2021-04-30 15:03:59.503 UPDATE
