List all users groups and the actual users in them in sql

eballinger
Contributor

‎05-16-2025 10:51 AM

We have a bunch of cloud AD groups in Databricks and I can see what users are in each group by using the user interface Manage Account -> Users and groups -> Groups

I would like to be able to produce this full list in SQL. I have found the below code online to give me exactly what I want but it is in Python. There doesnt seem to be a way in SQL to do this? What I was thinking is if the SQL was a little complex I would just put it inside a SQL View and then my report could just call this view. 

Also I tried to create a UDF that would use the code below (because you can use Python) but it gave me a error that   [FEATURE_UNAVAILABLE] Python UDTF is not supported in your environment. To use this feature, please contact Databricks Support. SQLSTATE: 56038

Thanks

 

 

# Databricks notebook source
from pyspark.sql.functions import lit

# COMMAND ----------
df_groups = spark.sql("""SHOW GROUPS""")

# COMMAND ----------
df_users = spark.sql("""SHOW USERS""")

# COMMAND ----------
result = spark.createDataFrame([], "name: string, directGroup: boolean, user: string")
for user in df_users.collect():
    df_user_groups = spark.sql(f"""SHOW GROUPS WITH USER `{user.name}`""").withColumn("user", lit(user.name))
    result = result.unionAll(df_user_groups)

# COMMAND ----------

display(result) # Here you can create temp view and select data
0 Kudos