Just that , is there a way whilst connected to the master DB of an Azure PaaS sql server to list the server admin and AAD admin accounts using TSQL?
Thanks.
Just that , is there a way whilst connected to the master DB of an Azure PaaS sql server to list the server admin and AAD admin accounts using TSQL?
Thanks.
@backtothefuture-4787 Thank you for reaching out.
Unfortunately, there is no DMV to fetch this information directly.
You will need to use PowerShell or CLI to get this information.
However, I found a nifty script on SQLServerCentral that I modified to return principals from dbmanager and loginmanager roles only.
It seems to work for me when I ran it on my master database, but I haven't tested for all scenarios.
Please check and modify as per your needs.
;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 ('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);
Results

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.
Thanks Kalyan , that first CTE is the one that seems to return the admin account details, but it seems to do so by looking for principals that have typically what would be admin privileges , and assumes that in the master these are the privileges indicative of their being admin accounts. I suspect it is possible (but unlikely/undesirable) for other accounts to have one or more of these permissions.
The other CTE returns called 'fixed' just returns members of dbmanager and loginmanager roles so not really relevant.
So for me the script doesn't look like it meets the requirement but thanks for your reply anyway!
I specifically looked for those 2 roles only because, the principal needs to be a member of both of them to be an admin.
Unlike SQL Server or Managed Instance, Azure SQL Database doesn't have a sysadmin role.
So members of these special master database roles (dbmanager and loginmanager) for Azure SQL Database have authority to create and manage databases or to create and manage logins.
9 people are following this question.