Forum Discussion

Denise Child's avatar
Denise Child
Iron Contributor
Jan 20, 2023

Azure SQL server get all users and their roles

Hello, what are the commands to get a list of all users that have access to an Azure SQL Server?

We are using AAD - Universal with MFA to login.

 

I have tried several different ways using Master and from the Database, but most of us are db_owners.

So it only shows dbo for login.

 

How do we get all the users that have this role?

 

Thanks for any help.

Denise

azure sql
azure sql database
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jan 21, 2023

    Denise Child 

    Please try this:

     

    List of Users:

    SELECT
	name
	,type_desc
	,create_date
	,CASE WHEN is_disabled = 1 THEN 'Disabled'
	      ELSE 'Enabled'
	 END AS is_disabled
FROM sys.sql_logins
ORDER BY name

     

    List of Users by role:

     

    SELECT 
    DP1.name AS DatabaseRoleName
    ,ISNULL(DP2.name, 'No members') AS DatabaseUserName
    ,DP2.principal_id
    ,DP2.create_date
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
ORDER BY DP1.name, ISNULL(DP2.name, 'No members');

     

    • Denise Child's avatar
      Denise Child
      Iron Contributor
      Jan 23, 2023

      This will return db_owner but I need to know who are ALL the db_owners.

      I can get it to return my account showing I am a db_owner on the Database with this command:

      IF IS_ROLEMEMBER ('db_owner') = 1
      print 'Current user is a member of the db_owner role'
      ELSE IF IS_ROLEMEMBER ('db_owner') = 0
      print 'Current user is NOT a member of the db_owner role'
      ELSE IF IS_ROLEMEMBER ('db_owner') IS NULL
      print 'ERROR: The database role specified is not valid.';

      • Denise Child's avatar
        Denise Child
        Iron Contributor
        Jan 26, 2023

        I opened a ticket with Microsoft. In Azure Portal go to the SQL Database and then click on Properties, in there we have Active Directory Admin. That is a security account that was created. We then go to Azure Active Directory and look for that Group and click on Members. Those are the DBO's for the Azure SQL Database. In addition, we have other DBO's that are external and those can be found by running the script listed by @Kidd_lp

        SELECT
        DP1.name AS DatabaseRoleName
        ,ISNULL(DP2.name, 'No members') AS DatabaseUserName
        ,DP2.principal_id
        ,DP2.create_date
        FROM sys.database_role_members AS DRM
        RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id
        LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id
        WHERE DP1.type = 'R'
        ORDER BY DP1.name, ISNULL(DP2.name, 'No members');


    • Senthil77's avatar
      Senthil77
      Copper Contributor
      Oct 21, 2023

      Denise Child 

       

      How do we get  the users list in each database levels and their last logins details.?

       

      Thanks

Resources