Imagine you just inherited a Snowflake database with Epic EHR data at the same time a new enterprise legal restriction requires you to lock down caregiver access to Epic intellectual property.

No problem.

Only it's not one database. It's three - DEV, PREPROD, and PROD - with six years of permission changes made by people who are no longer on the team, made for reasons no one remembers, and made without a convention anyone documented.

You do the math:

  • 60 schemas
  • 729 tables
  • 239 views
  • 532 stored procedures
  • 8 file formats

To untangle this by hand? Two weeks minimum. Probably more. And that's if you don't introduce errors — which, at this scale, you will.

This is where you have to make a call: patch or fix?

The Decision to Start Over

Incremental fixes were tempting. They always are. But when you sit with the scope of what's in front of you, you realize that every small fix leaves the underlying mess intact. Six years from now, someone else will be sitting where you're sitting, facing the same problem with even more accumulated drift.

So you choose the harder path: a complete overhaul of roles and permissions across all three environments.

How You Actually Get Through It

The work split into two tracks running simultaneously, and both had to go well.

The first was convention design. Before you write a single grant statement, you need to know what right looks like. This means sitting in DataOps conversations and actively pushing back on ideas that sound clean in theory but fall apart in practice. The crucial move here: you run hands-on tests during the design phase, not after. Every edge case you find before the standard is finalized is one you don't have to undo later.

The second was automation. Here's the uncomfortable truth about working at this scale: manual grant management is not just slow, it's a trap. You will make errors. You will miss things. You will blow your cognitive budget on the wrong problems.

Instead, you build a prompt-driven approach: SQL object queries feed input variables into a chat prompt, which generates the resulting grant and revoke statements. By the time you're done, you've produced over 8,800 commands. What would have been roughly two weeks of painstaking, error-prone manual work gets done in two days of systematic execution.

Zero downtime. On schedule.

What the Convention Actually Looks Like

When you finally have a model that works, it rests on two core principles that you'll want to hold onto:

Assign at the schema level, not the object level. If you're granting permissions table by table, you've already lost. The schema is your unit of governance.

Create a distinct Admin, Writer, and Reader role for every schema. Not shared across schemas. Not inherited from a parent. Distinct, per schema, every time. Note: this approach applies when every schema has different owners, particular to this case.

From there, you're working with three role types:

  • Team roles — account-level roles for the people who support the database day to day.
  • Database roles — Admin, Writer, and Reader roles scoped to a specific database, unique per schema. These are the backbone.
  • Functional roles — account-level roles for consumers: users, service accounts, downstream systems. Named for what they do, not how the database is built.

The elegance is in how they connect. In PROD, you grant a Database Reader or Writer role to a Functional role. Admin access goes to a Functional role only when a service account genuinely needs to create Stages dynamically. In DEV, you have more flexibility — grant what the work actually requires.

Role Assignment Diagram

graph TD;

%% Colorize Database Roles
style SCHEMANM_DBR_ADMIN fill:#cfc,stroke:#0c0,color:#333
style SCHEMANM_DBR_W fill:#cfc,stroke:#0c0,color:#333
style SCHEMANM_DBR_RO fill:#cfc,stroke:#0c0,color:#333

%% Colorize Team Roles
style DATABASENM_DB_ADMIN fill:#ccf,stroke:#00c,color:#333
style DATABASENM_DB_W fill:#ccf,stroke:#00c,color:#333
style DATABASENM_DB_RO fill:#ccf,stroke:#00c,color:#333

%% Colorize Functional Roles
style SCHEMANM_FR_US_PROD_ADMIN fill:#fcc,stroke:#c00,color:#333
style SCHEMANM_FR_US_PROD_DEVENG fill:#fcc,stroke:#c00,color:#333

%% Database to Schema
DATABASENM_DB[Database: DATABASENM_DB] --> STG_DATABASENM_SCHEMANM[Schema: STG_DATABASENM_SCHEMANM]

%% Schema to Roles
STG_DATABASENM_SCHEMANM -->|Team Role| DATABASENM_DB_ADMIN
STG_DATABASENM_SCHEMANM -->|Database Role| SCHEMANM_DBR_ADMIN
STG_DATABASENM_SCHEMANM -->|Database Role| SCHEMANM_DBR_W
STG_DATABASENM_SCHEMANM -->|Database Role| SCHEMANM_DBR_RO

%% Privileges
DATABASENM_DB_ADMIN -->|Privileges| OWNERSHIP
SCHEMANM_DBR_ADMIN -->|Privileges| PrivDA["CREATE FILE FORMAT, CREATE PROCEDURE, CREATE STAGE, CREATE TABLE, CREATE TEMPORARY TABLE, CREATE VIEW, OWNERSHIP - FUTURE FILE FORMAT, OWNERSHIP - FUTURE PROCEDURE, OWNERSHIP - FUTURE STAGE, OWNERSHIP - FUTURE TABLE, OWNERSHIP - FUTURE VIEW"]
SCHEMANM_DBR_W -->|Privileges| PrivW["DELETE - FUTURE TABLE, INSERT - FUTURE TABLE, TRUNCATE - FUTURE TABLE, UPDATE - FUTURE TABLE, USAGE - FUTURE FILE FORMAT, USAGE - FUTURE PROCEDURE, USAGE - FUTURE STAGE"]
SCHEMANM_DBR_RO -->|Privileges| PrivRO["SELECT - FUTURE TABLE, SELECT - FUTURE VIEW, USAGE"]

%% Database Roles to Database Roles
SCHEMANM_DBR_RO -->|DBR to DBR Role| SCHEMANM_DBR_W
SCHEMANM_DBR_W -->|DBR to DBR Role| SCHEMANM_DBR_ADMIN

%% Database Roles to Team Account Roles
SCHEMANM_DBR_ADMIN -->|DBR to Team Role| DATABASENM_DB_ADMIN
SCHEMANM_DBR_W -->|DBR to Team Role| DATABASENM_DB_W
SCHEMANM_DBR_RO -->|DBR to Team Role| DATABASENM_DB_RO

%% Database Roles to Functional (External) Roles
SCHEMANM_DBR_ADMIN -->|DBR to Functional Role| SCHEMANM_FR_US_PROD_ADMIN
SCHEMANM_DBR_RO -->|DBR to Functional Role| SCHEMANM_FR_US_PROD_DEVENG

%% Team Roles to Users
DATABASENM_DB_ADMIN -->|Team Users| ADMINS[Most team members ...]
DATABASENM_DB_W -->|Team Accounts| WRITERS[same_service_account_for_DEV_and_PROD.org]
DATABASENM_DB_RO -->|Team Users| READERS[Not as many team members, if any ...]

%% Functional Roles to Users
SCHEMANM_FR_US_PROD_ADMIN -->|External Accounts| SERVICE_ACCOUNT[service_account_for_PROD.org]
SCHEMANM_FR_US_PROD_DEVENG -->|External Users| HUMANS["human1@company.org, human2@company.org, ..."]

What You Walk Away With

At the end of this, you have something you didn't have before: a documented convention and a role assignment diagram that other database owners in your enterprise can actually use. Not a theoretical framework. A real reference artifact, built from implementation experience.

That matters more than it might seem. Every new person who joins the team without a documented convention is a potential source of drift. Every judgment call made in the dark is a future untangling job. What you've built is a stable reference point — so the next person doesn't have to start from scratch.

The Takeaways Worth Carrying Forward

If you find yourself in a situation like this, a few things will serve you well:

Compliance pressure is a gift, if you use it right. A legal mandate gave this project the urgency it needed to get prioritized. The technical debt that might have been deferred for another two years got addressed because it had to be. Don't waste that forcing function on a patch.

At hundreds of objects across multiple environments, automation isn't a nice-to-have. It's the only path that ends without errors.

Documented conventions are the only thing that prevents this from happening again. Role assignment diagrams, decision records, naming conventions — these are what give rotating teams a fighting chance at consistency.

And test before you codify. The standard should reflect what actually works, not what sounds right in a meeting. Find the edge cases while you can still fix them cheaply.

The problem you inherited was six years in the making. The fix took two days. The difference was having the right approach — and the courage to do it properly instead of just making it slightly less broken.