topic Re: Need Help - System tables that contains all databricks users, service principal details !! in Administration & Architecture

Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 07:20:06 GMT

Hi all - I am trying to create a dashabord where I need to list down all users and service principals along with groups and understand their databricks usages. Is there any table available in Databricks that contains user, service principal details. Please help me on this.

Re: Need Help - System tables that contains all databricks users, service principal details !!

mukul1409 — Wed, 31 Dec 2025 08:54:25 GMT

Hi @Sanjeeb2024

Databricks does not provide a single built in table that lists users and service principals along with their usage, but you can build this by combining a few supported sources. User and service principal details can be retrieved using the system information schema tables such as system information users, system information service principals, and system information group members, which give identities and group mappings. For usage and activity, you can use the system access audit logs tables to understand actions performed by users and service principals, and the system compute usage tables to analyze cluster and SQL usage. By joining identity information from the information schema with audit logs and usage tables, you can build a dashboard that shows users, service principals, their group memberships, and how they are using Databricks resources.

Re: Need Help - System tables that contains all databricks users, service principal details !!

emma_s — Wed, 31 Dec 2025 11:29:16 GMT

Hi,

Hi, there isn't a table which shows mappings of Users to groups, you can only see this through the groups editing. You can, however, check grants on tables to see which groups or users have been granted permission to tables. The following query is an example of how you can see this:

SELECT 'catalog' AS level, catalog_name, NULL AS schema_name, NULL AS table_name, grantee, privilege_type FROM system.information_schema.catalog_privileges UNION ALL SELECT 'schema' AS level, catalog_name, schema_name, NULL AS table_name, grantee, privilege_type FROM system.information_schema.schema_privileges UNION ALL SELECT 'table' AS level, table_catalog AS catalog_name, table_schema AS schema_name, table_name, grantee, privilege_type FROM system.information_schema.table_privileges ORDER BY level, catalog_name, schema_name, table_name, grantee, privilege_type;

In terms of analysing system usage by user, here are some queries that should help you:

Analysing Usage For SQL Warehouses, attributing cost based on time

%sql WITH wh_hourly_usd AS ( SELECT u.usage_metadata.warehouse_id AS warehouse_id, date_trunc('hour', u.usage_start_time) AS hour_ts, SUM(u.usage_quantity * p.pricing.effective_list.default) AS usd_hour FROM system.billing.usage AS u JOIN system.billing.list_prices AS p ON p.sku_name = u.sku_name AND u.usage_end_time >= p.price_start_time AND (p.price_end_time IS NULL OR u.usage_end_time < p.price_end_time) WHERE u.usage_metadata.warehouse_id IS NOT NULL GROUP BY ALL ), wh_hourly_load AS ( SELECT h.compute.warehouse_id AS warehouse_id, date_trunc('hour', h.start_time) AS hour_ts, h.executed_by AS user_email, SUM(h.total_task_duration_ms) AS task_ms FROM system.query.history AS h WHERE h.compute.type = 'WAREHOUSE' AND h.execution_status IN ('FINISHED','FAILED','CANCELED') GROUP BY ALL ), user_share AS ( SELECT l.warehouse_id, l.hour_ts, l.user_email, l.task_ms / NULLIF(SUM(l.task_ms) OVER (PARTITION BY l.warehouse_id, l.hour_ts), 0) AS share FROM wh_hourly_load l ), attributed AS ( SELECT s.warehouse_id, s.user_email, SUM(s.share * u.usd_hour) AS usd_attributed FROM user_share s JOIN wh_hourly_usd u ON s.warehouse_id = u.warehouse_id AND s.hour_ts = u.hour_ts GROUP BY s.warehouse_id, s.user_email ) SELECT warehouse_id, user_email, usd_attributed FROM attributed ORDER BY usd_attributed DESC;

Analysing costs for jobs

%sql -- Estimated $USD per job run_as identity SELECT usage.usage_metadata.job_id AS job_id, usage.usage_metadata.job_run_id AS job_run_id, usage.identity_metadata.run_as AS run_as_identity, SUM(usage.usage_quantity * prices.pricing.effective_list.default) AS est_cost_usd FROM system.billing.usage AS usage JOIN system.billing.list_prices AS prices ON prices.sku_name = usage.sku_name AND usage.usage_end_time >= prices.price_start_time AND (prices.price_end_time IS NULL OR usage.usage_end_time < prices.price_end_time) WHERE usage.usage_metadata.job_id IS NOT NULL GROUP BY ALL ORDER BY est_cost_usd DESC;

Costs for all purpose clusters, note for all purpose clusters you can only attribute costs to the person who set it up, you may want to thing about a tagging strategy to attribute to teams etc

SELECT usage.usage_metadata.cluster_id AS cluster_id, usage.custom_tags['creator'] AS creator_tag, SUM(usage.usage_quantity) AS dbus FROM system.billing.usage AS usage WHERE usage.usage_metadata.cluster_id IS NOT NULL GROUP BY ALL ORDER BY dbus DESC;

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 11:54:00 GMT

Thanks. The cost queries are helpful. However I am looking for a report which will provide me all users and service principals created in Databricks, can you share me how to get that list.

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 11:54:59 GMT

I already explore this but not getting the desired information.

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 12:23:45 GMT

Hi @mukul1409 - I do not find any users table under information_schema under my Databricks account. Can you please validate it at your end and share me a screenshot. Do not send any data, just a catalog screenshot should be ok. I checked Databricks AI assistant, it provide me the same SQL, I think it is pointing to old data.

Regards - San

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 12:37:36 GMT

Hi @mukul1409 - are you able to validate at your end to ensure you have users table under information_schema. Even if I tried to check in Databricks Free edition where I am the account admin and not able locate this table. Requesting you, please validate once to check this? See the screenshot from Databrick Free Edition.

I am able to see all other tables under system database, so need to understand whether the issue is with my end or these tables are depreciated.

Regards - San

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 12:38:38 GMT

Another screenshot:

Re: Need Help - System tables that contains all databricks users, service principal details !!

mukul1409 — Wed, 31 Dec 2025 12:49:47 GMT

Hi @Sanjeeb2024

In Databricks Free Edition , the identity related system tables such as users, service principals, groups, and group members are not exposed, even if you are the account admin. These tables are part of Databricks system tables and are only available in paid workspaces with Unity Catalog enabled and system tables turned on at the account level.

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 12:54:54 GMT

Ok, thanks, let me check with account admin for our Enterprise edition.

Re: Need Help - System tables that contains all databricks users, service principal details !!

emma_s — Wed, 31 Dec 2025 13:44:54 GMT

Hi, I can't find any reference to a user system table in our docs. Instead the recommended approach is to use the API to return users, groups and service principals. You can either run this using the Workspace Client if you only have worspace admin permissions or the Account client if you have account admin permissions. Example code below:

from databricks.sdk import AccountClient # If env vars are set, this picks them up automatically a = WorkspaceClient() # List identities users = list(a.users.list()) groups = list(a.groups.list()) service_principals = list(a.service_principals.list()) print(f"Users: {len(users)}") for u in users[:10]: print(f"- {u.user_name}") print(f"\nGroups: {len(groups)}") for g in groups[:10]: print(f"- {g.display_name}") print(f"\nService Principals: {len(service_principals)}") for sp in service_principals[:10]: print(f"- {getattr(sp, 'display_name', getattr(sp, 'application_id', 'unknown'))}") def get_group_members_by_id(group_id: str): w = WorkspaceClient() group = w.groups.get(id=group_id) # SCIM read of the group members = group.members or [] return members # List users and service principals in each group for group in a.groups.list(): print(f"Group: {group.display_name}") members = list(get_group_members_by_id(group.id)) for member in members: print(f" - {member.type}: {getattr(member, 'display', getattr(member, 'user_name', 'unknown'))}")

Re: Need Help - System tables that contains all databricks users, service principal details !!

Sanjeeb2024 — Wed, 31 Dec 2025 14:10:15 GMT

Thank you @emma_s . Appreciated your help. I will give a try and update.

Re: Need Help - System tables that contains all databricks users, service principal details !!

Yourstruly — Wed, 21 Jan 2026 18:19:57 GMT

I bet you asked AI for this... lol

There are no such tables, they are made up.

Re: Need Help - System tables that contains all databricks users, service principal details !!

Akash_Nimbalkar — Tue, 24 Mar 2026 06:57:19 GMT

Hi Sanjeeb,
Please let me know what worked for you.

Re: Need Help - System tables that contains all databricks users, service principal details !!

dbx_user — Fri, 18 Sep 2026 02:06:58 GMT

THIS POST NEEDS TO BE REMOVED FROM THE WEBSITE, the information is blatantly wrong and no search engines are accepting that it is. This is making finding real information about user information impossible.

INCORRECT AND INVALID

Re: Need Help - System tables that contains all databricks users, service principal details !!

dbx_user — Fri, 18 Sep 2026 02:10:11 GMT

This information is false and misleading, it is breaking search engine algorithms and making finding valid information about usage tables near imposible.

THIS INFORMATION IS INVALID

THIS POST SHOULD BE DELETED

Re: Need Help - System tables that contains all databricks users, service principal details !!

dans-vander — Fri, 18 Sep 2026 18:20:43 GMT

Hello @Sanjeeb2024 ,

Why do you need this to be done using system tables?

If you have Account Admin access, there is a REST API that allows you to list all users, groups, and service principals for the account:

https://docs.databricks.com/api/scim/v1/user
https://docs.databricks.com/api/scim/v1/service-principal
https://docs.databricks.com/api/scim/v1/group

You can extract this information through a job using a service principal with Account Admin privileges, load it into a DataFrame, and then generate your own tables.
Please check if this meets your needs.

Regards,

Danilo Van der Linden