question

backtothefuture-4787 avatar image
0 Votes"
backtothefuture-4787 asked KalyanChanumolu-MSFT commented

Identify server admin and Active Directory Admin using TSQL

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.

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KalyanChanumolu-MSFT avatar image
0 Votes"
KalyanChanumolu-MSFT answered KalyanChanumolu-MSFT commented

@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

130049-image.png


If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.


image.png (22.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

0 Votes 0 ·

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.

0 Votes 0 ·