← All posts
Data Engineering10 min read

Snowflake Dormant Users: Find Accounts Nobody Uses (SQL)

Dormant Snowflake accounts keep their roles and warehouse access. Run these ACCOUNT_USAGE queries to find them, disable them safely and stop the drift.

To find Snowflake dormant users, query SNOWFLAKE.ACCOUNT_USAGE.USERS and filter on last_success_login (falling back to created_on for accounts that never logged in), then cross-check LOGIN_HISTORY, QUERY_HISTORY and GRANTS_TO_USERS. Anything idle for 90 days or more that still holds a role is a candidate for disabling. Disable first and drop later, because disabling is reversible and dropping is not.

Most Snowflake accounts we audit have more human and service identities than anyone can name. Contractors have left, analysts have changed teams, and a proof-of-concept service user from two years ago still holds a powerful role. None of these accounts cost anything by themselves. But each one is a working credential with live privileges that nobody is watching. This post gives you the SQL to find them in under five minutes, a framework for deciding what to do with each one, and a way to stop the list growing back.

Why Do Snowflake Dormant Users Pile Up?

Because nothing in Snowflake removes a user when the person or integration goes away. Users are created in a hurry (a new hire, a BI connection, a dbt runner) and offboarding depends on someone remembering to run an ALTER or DROP. In a growth-stage company, that someone usually does not exist.

A pattern we see repeatedly in early-stage companies is a user list that mixes four populations: current staff, departed staff, forgotten service accounts, and accounts created for a trial that never ended. Only the first is meant to be there.

The risk is real. Verizon's 2025 DBIR found that the use of compromised credentials was an initial access vector in 22% of the breaches reviewed. Secondary summaries of the 2026 edition report credential abuse present anywhere in 39% of breaches. A dormant account is the ideal target for that kind of attack. Its owner will never notice odd activity because they are not using it. Revefi's write-up on dormant accounts makes the same point: unusual behaviour such as sudden queries or large exports may not raise red flags on accounts that are rarely used.

There is a cost angle too. A dormant user holding USAGE on a warehouse can still log in and resume it, so compute can be started by an identity nobody owns.

Data engineer running SQL to find Snowflake dormant users by days idle


📺 Watch: The easiest way to find inactive Snowflake users to avoid security issues

The easiest way to find inactive Snowflake users to avoid security issues


How Do You Find Snowflake Dormant Users With SQL?

Start with the USERS view in ACCOUNT_USAGE. It lists every user in the account, including the last successful login, whether the account is disabled, and which credential types it holds. Run this as ACCOUNTADMIN, or as a role that has been granted imported privileges on the SNOWFLAKE database.

SELECT
    name,
    type,
    default_role,
    created_on,
    last_success_login,
    DATEDIFF('day',
             COALESCE(last_success_login, created_on),
             CURRENT_TIMESTAMP()) AS days_idle,
    has_password,
    has_rsa_public_key,
    has_mfa
FROM snowflake.account_usage.users
WHERE deleted_on IS NULL
  AND COALESCE(disabled::string, 'false') <> 'true'
  AND COALESCE(last_success_login, created_on)
        < DATEADD('day', -90, CURRENT_TIMESTAMP())
ORDER BY days_idle DESC;

The COALESCE matters. A user who was created eight months ago and never logged in has a NULL last_success_login, and a plain less-than comparison silently drops them. Those never-used accounts are often the most dangerous, because they were provisioned with a role and a temporary password that nobody ever changed.

Two caveats before you act on the list:

  • ACCOUNT_USAGE views lag real time, by up to a couple of hours for USERS. Do not use them to confirm that something happened a minute ago.
  • Snowflake's documentation notes that LAST_SUCCESS_LOGIN can differ from the value in SHOW USERS because near-real-time and historical logins are recorded differently. For a borderline user, run SHOW USERS as a second check.

Which of them hold privileged roles?

A list of 40 idle users is a chore. A list of three idle users with administrative roles is an incident. Join the idle list to GRANTS_TO_USERS to rank by blast radius:

WITH idle AS (
    SELECT name, last_success_login, created_on
    FROM snowflake.account_usage.users
    WHERE deleted_on IS NULL
      AND COALESCE(disabled::string, 'false') <> 'true'
      AND COALESCE(last_success_login, created_on)
            < DATEADD('day', -90, CURRENT_TIMESTAMP())
)
SELECT
    i.name,
    g.role,
    i.last_success_login
FROM idle i
JOIN snowflake.account_usage.grants_to_users g
  ON g.grantee_name = i.name
 AND g.deleted_on IS NULL
ORDER BY
    CASE WHEN g.role IN ('ACCOUNTADMIN','SECURITYADMIN','SYSADMIN')
         THEN 0 ELSE 1 END,
    i.name;

Anything at the top of that result with ACCOUNTADMIN or SECURITYADMIN should be dealt with today, not in the next quarterly review.

Which Dormant Accounts Are Actually Dangerous?

Last login is a blunt instrument. It misses two cases that matter: accounts that authenticate but never query, and service users that are alive but have no owner. Cross-check query activity as well:

SELECT
    u.name,
    u.type,
    MAX(q.start_time) AS last_query
FROM snowflake.account_usage.users u
LEFT JOIN snowflake.account_usage.query_history q
  ON q.user_name = u.name
 AND q.start_time > DATEADD('day', -90, CURRENT_TIMESTAMP())
WHERE u.deleted_on IS NULL
GROUP BY u.name, u.type
HAVING MAX(q.start_time) IS NULL
ORDER BY u.type, u.name;

The time filter sits inside the join condition on purpose. It keeps the scan bounded instead of reading the full query history.

Once you have both lists, sort each account into a bucket. This is the framework we use in access reviews:

Account typeSignalAction
Human, never logged inNULL last_success_login, older than 14 daysDisable now, drop after 30 days
Human, idle 90+ daysStale login, no queriesConfirm with their manager, disable
Human, admin role, idle 30+ daysPrivileged and unusedRevoke the role today, then disable
Service user, no recent logins or queriesIntegration may be deadFind the owner, then rotate credentials or disable
Service user, logging in but password-onlyActive but weakMove to key-pair or another non-password method

The last row is not strictly about dormancy, but it belongs in the same review. Snowflake is phasing out single-factor password sign-in, and service and legacy service users will lose the ability to authenticate with a password. Any password-only service identity you find now is a migration to plan.

Running those queries for one account is fine. Checking every model, grant reference and committed credential in your dbt project is a different job, and a manual one. fintel-scan is a free, open-source (MIT) CLI that runs a set of project-level checks locally, with no warehouse connection: uvx fintel-scan. It will not replace the account-level SQL above, because that needs a live Snowflake connection. It covers the dbt project side of the same governance problem.

How Do You Disable Snowflake Dormant Users Safely?

Disable first. Do not drop. Disabling prevents the user from logging in and running queries, and you can reverse it in one statement if someone shouts.

ALTER USER jane_doe SET DISABLED = TRUE;
ALTER USER jane_doe SET COMMENT = 'Disabled 2026-11-19: dormant 90d, access review';

The comment is your audit trail. Six months later, nobody will remember why the account was switched off, and this note answers the question.

A safe sequence for the first review:

  1. Export the candidate list to a ticket or sheet with a named approver for each row.
  2. Revoke privileged roles first on any idle admin, before disabling anything else.
  3. Disable the batch, with a comment on every user.
  4. Wait 30 days. Watch for failed logins from disabled users in LOGIN_HISTORY. A burst of failures means a live integration was hiding behind that identity.
  5. Drop the confirmed-dead accounts.

Step 4 saves you from the one bad outcome. In our experience, the identity that looks dormant but turns out to power a monthly finance export is nearly always a service account with no documentation. If a finance close fails on the 1st of the month because you dropped its user on the 28th, you have traded a theoretical risk for a real outage. Disabling and waiting through one full business cycle avoids that.

Ownership is less of a worry than people expect. Objects in Snowflake are owned by roles, not users, so dropping a user does not orphan their tables. What you lose is the login, the user-level settings, and the history of who that person was.

Team reviewing a Snowflake dormant users access review checklist together

How Do You Stop Snowflake Dormant Users Coming Back?

A one-off clean-up decays within a quarter. Make the check recurring and visible. The simplest version is a scheduled Snowflake task that snapshots the idle list into a governance table every week:

CREATE TABLE IF NOT EXISTS governance.access.dormant_user_snapshot (
    snapshot_date DATE,
    user_name STRING,
    user_type STRING,
    last_success_login TIMESTAMP_LTZ,
    days_idle NUMBER
);

CREATE OR REPLACE TASK governance.access.dormant_user_check
  WAREHOUSE = admin_xs_wh
  SCHEDULE = 'USING CRON 0 7 * * 1 UTC'
AS
  INSERT INTO governance.access.dormant_user_snapshot
  SELECT
      CURRENT_DATE(),
      name,
      type,
      last_success_login,
      DATEDIFF('day',
               COALESCE(last_success_login, created_on),
               CURRENT_TIMESTAMP())
  FROM snowflake.account_usage.users
  WHERE deleted_on IS NULL
    AND COALESCE(disabled::string, 'false') <> 'true'
    AND COALESCE(last_success_login, created_on)
          < DATEADD('day', -90, CURRENT_TIMESTAMP());

ALTER TASK governance.access.dormant_user_check RESUME;

The task's owning role needs imported privileges on the SNOWFLAKE database. Use an X-Small warehouse, since the query is tiny.

Then add three habits that make it stick:

  • Put a test on it. If you already run dbt against Snowflake, expose the snapshot table as a source and add a test that fails when any user with an admin role has been idle for more than 30 days. Our guide to dbt test severity explains why that test must be set to error, not warn, or it will be ignored.
  • Tie it to offboarding. Provision users from your identity provider with SCIM, so an HR departure disables the Snowflake user automatically. The query then becomes a safety net instead of the primary control.
  • Review monthly. Ten minutes with the snapshot table, an approver per row, and a note in the ticket. That is the whole process.

If your data stack has grown faster than its governance, this is rarely the only gap. Dormant users tend to travel with unmanaged service credentials, broad role grants and undocumented ownership. If you want a second pair of eyes on that, explore our services to see how we help businesses like yours.

Frequently Asked Questions

Q: How do I find users who have never logged in to Snowflake?

A: Query SNOWFLAKE.ACCOUNT_USAGE.USERS where last_success_login IS NULL and deleted_on IS NULL. Add a created_on filter (for example, older than 14 days) so you do not flag brand-new accounts. Use COALESCE(last_success_login, created_on) in your idle calculation so these users are not missed.

Q: How long should a Snowflake user be inactive before you disable it?

A: 90 days is a common starting threshold for standard users, and 30 days for anyone holding ACCOUNTADMIN or SECURITYADMIN. Choose the number your security policy or auditors expect, then apply it consistently. Disable first and drop only after a further 30-day waiting period.

Q: Should I disable or drop dormant Snowflake users?

A: Disable first. Setting DISABLED = TRUE blocks login and is reversible, while DROP USER is permanent. Dropping only makes sense after you have confirmed, usually over a full month-end cycle, that no integration or person depends on the account.

Q: Does a dormant Snowflake user cost money?

A: The user object itself is free. The risk is that an idle account with warehouse USAGE can still resume compute if someone or something signs in with its credentials. Every credit spent should map to an owner, and dormant accounts break that mapping.

Q: How often does the ACCOUNT_USAGE USERS view update?

A: ACCOUNT_USAGE views have a latency, and for USERS it can be up to a couple of hours. That is fine for a weekly review. For a user who has just logged in, or one you are investigating right now, confirm with SHOW USERS or LOGIN_HISTORY.

Closing the Gap Between Access Reviews

The challenge is not running the query once. It is keeping Snowflake dormant users, orphaned service accounts and over-broad roles under control while the team, the integrations and the data volumes keep growing. At Fintel Analytics, we have helped growth-stage fintech, payments and e-commerce teams audit their warehouse access, move the checks into dbt and scheduled tasks, and leave with a review process their next due diligence will accept. If you suspect your Snowflake user list has drifted, finding out now is far cheaper than finding out from an incident or an investor's security questionnaire.

← Back to all posts