Snowflake SQL Tutorial 158 - A New Role Can Do Nothing, And Nothing Tells You So

Snowflake SQL · Episode 158

In this post we are going to cover roles in Snowflake, which is where access control starts.

A role is a name that holds privileges. You grant privileges to the role, you grant the role to a user, and the user puts the role on to use them.

The interesting part is what a brand new role does not hold, and how quiet it is about that. Nothing on screen tells you a role can do nothing.

We will start with the roles the account already has and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 158 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's see what roles the account already has
2. Let's create a role with nothing but a name
3. Now let's put it on and have a look around
4. Two refusals, worded very differently on purpose
5. Now three roles, stacked
6. So what does CURRENT_ROLE say when you wear the parent?
7. Can a role be granted to itself?
8. Now secondary roles, and a grid that is not an answer
9. A few small ones worth having seen
10. Now the one that bites: dropping a role something hangs off
11. One last thing: let's put the account back

Let's see what roles the account already has

There is no sample table in this post. Everything until now in this course ran on tables, and from here on we are working on the account itself.

Where we are running

-- No sample table in this post.
-- A role is an account-level object, so there is nothing to create first.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;
USE ROLE ACCOUNTADMIN;

So the first question is what roles are here already.

Query

SHOW ROLES;

Result7 rows · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-09-21 18:33:41.930000-07:00	ACCOUNTADMIN	Y	Y	N	1	0	2		Account administrator can manage all aspects of the account.	N
2026-09-21 18:33:42.322000-07:00	ORGADMIN	N	N	N	1	0	0		Organization administrator can manage organizations and accounts in organizations	N
2026-09-21 18:33:41.959000-07:00	PUBLIC	N	N	Y	0	0	1		Public role is automatically available to every user in the account.	N
2026-09-21 18:33:41.947000-07:00	SECURITYADMIN	N	N	Y	0	1	1		Security administrator can manage security aspects of the account.	N
2026-09-21 18:33:47.878000-07:00	SNOWFLAKE_LEARNING_ROLE	N	N	Y	0	1	0	ACCOUNTADMIN	Created by Snowflake during account provisioning	N
2026-09-21 18:33:41.953000-07:00	SYSADMIN	N	N	Y	0	1	0		System administrator can create and manage databases and warehouses.	N
2026-09-21 18:33:41.941000-07:00	USERADMIN	N	N	Y	0	1	0		User administrator can create and manage users and roles	N

Seven roles, and 11 columns about them. The famous five are in there, and so are two others.

ORGADMIN sits above the account rather than inside the usual tree. And SNOWFLAKE_LEARNING_ROLE is one I did not make: its comment says it was created by Snowflake during account provisioning. Worth saying out loud, because the last grid of this post shows this same list again.

Here are just the ones whose names end in ADMIN.

Query

SHOW ROLES LIKE '%ADMIN';

Result5 rows · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-09-21 18:33:41.930000-07:00	ACCOUNTADMIN	Y	Y	N	1	0	2		Account administrator can manage all aspects of the account.	N
2026-09-21 18:33:42.322000-07:00	ORGADMIN	N	N	N	1	0	0		Organization administrator can manage organizations and accounts in organizations	N
2026-09-21 18:33:41.947000-07:00	SECURITYADMIN	N	N	Y	0	1	1		Security administrator can manage security aspects of the account.	N
2026-09-21 18:33:41.953000-07:00	SYSADMIN	N	N	Y	0	1	0		System administrator can create and manage databases and warehouses.	N
2026-09-21 18:33:41.941000-07:00	USERADMIN	N	N	Y	0	1	0		User administrator can create and manage users and roles	N

Query

SELECT "name", "comment", "owner", "granted_to_roles", "granted_roles"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result5 rows · 5 columns

name	comment	owner	granted_to_roles	granted_roles
ACCOUNTADMIN	Account administrator can manage all aspects of the account.		0	2
ORGADMIN	Organization administrator can manage organizations and accounts in organizations		0	0
SECURITYADMIN	Security administrator can manage security aspects of the account.		1	1
SYSADMIN	System administrator can create and manage databases and warehouses.		1	0
USERADMIN	User administrator can create and manage users and roles		1	0

Now read the granted_to_roles column, because it tells you the shape of the tree. SECURITYADMIN, SYSADMIN and USERADMIN all say 1, so each of them hangs off something above it. ORGADMIN says 0. It is not underneath ACCOUNTADMIN at all. It is off to one side.

Reading the blank cells

The owner column on those built-in roles is blank and marked empty, which means an empty string rather than a real SQL NULL. Nobody owns a role Snowflake built in. On the roles we create below, the same column has a value.

There is one more thing the capture asked here, and its result is not on this page. It selects CURRENT_USER(), which names the account's user, and the builder cannot leave one column out of a grid. So here is the statement on its own.

Query (result withheld)

-- Who the session is, and what it is holding.
-- The result is withheld: this grid names the account's user.
SELECT CURRENT_USER()            AS THE_USER,
       CURRENT_ROLE()            AS THE_ROLE,
       CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES,
       CURRENT_WAREHOUSE()       AS THE_WAREHOUSE,
       CURRENT_VERSION()         AS THE_VERSION;

What it returned, and what matters for the rest of the post, is that the role is ACCOUNTADMIN and the version is the one in the footer.

Let's create a role with nothing but a name

A name and a comment, and nothing else at all.

Create

CREATE OR REPLACE ROLE R_T158 COMMENT = 'episode 158 - a role with no grants';

Result1 row · 1 column

status
Role R_T158 successfully created.

Query

SHOW ROLES LIKE 'R\\_T158';

Result1 row · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-10-06 11:23:26.856000-07:00	R_T158	N	N	N	0	0	0	ACCOUNTADMIN	episode 158 - a role with no grants	N

Query

SELECT "name", "comment", "owner", "granted_to_roles", "granted_roles",
       "assigned_to_users"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 6 columns · scroll →

name	comment	owner	granted_to_roles	granted_roles	assigned_to_users
R_T158	episode 158 - a role with no grants	ACCOUNTADMIN	0	0	0

There it is. No users, no roles granted to it, no roles it holds. Owned by ACCOUNTADMIN, because that is the role that created it.

Now let's ask what it is allowed to do.

Query

SHOW GRANTS TO ROLE R_T158;

Result0 rows · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by

Nothing. An empty grid. That role can do absolutely nothing on this account.

And nobody holds it either.

Query

SHOW GRANTS OF ROLE R_T158;

Result0 rows · 5 columns

created_on	role	granted_to	grantee_name	granted_by

Two empty grids, and they answer two different questions. SHOW GRANTS TO ROLE is what the role can do. SHOW GRANTS OF ROLE is who holds it.

Now let's put it on and have a look around

To wear a role you have to be granted it first.

Grant

-- The real user name is replaced with a stand-in name here.
GRANT ROLE R_T158 TO USER MY_USER_NAME;

Result1 row · 1 column

status
Statement executed successfully.

The result of that statement is just a status, so it is above. The grid that follows it in the capture is the one that says who holds the role now, and that grid names the account's user, so it is not on this page.

Query (result withheld)

-- Who holds this role.
-- The result is withheld: the grantee_name column is the account's user.
SHOW GRANTS OF ROLE R_T158;

What it reports is one row, with granted_to of USER and granted_by of ACCOUNTADMIN. You will see the same shape with a role in the grantee column further down, where there is nothing to withhold.

Now let's put the role on.

Use

USE ROLE R_T158;

Result1 row · 1 column

status
Statement executed successfully.

And now watch this, because it is the thing this post is really about.

Query

SELECT CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 4 columns

THE_ROLE	THE_DATABASE	THE_SCHEMA	THE_WAREHOUSE
R_T158	DEMO_DB	PUBLIC	COMPUTE_WH

CURRENT_ROLE() says the new role, so we are wearing it. And the database says DEMO_DB, the schema says PUBLIC, and the warehouse says COMPUTE_WH.

Every one of those answered. Nothing is empty and nothing is complaining.

But let's ask the other question again, wearing the role.

Query

SHOW GRANTS TO ROLE R_T158;

Result0 rows · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by

Still nothing. Both of those grids are true at the same time.

Why nothing warned us

The CURRENT_ functions report what the session is pointed at. They do not report what the role can reach. So a brand new role is silent rather than loud: it answers every question about where you are, and it can do nothing at all. SHOW GRANTS TO ROLE is the only one of those grids that told us the truth.

We are not going to run a SELECT under that role here. That is the next post, and it deserves the room.

Use

USE ROLE ACCOUNTADMIN;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 3 columns

THE_ROLE	THE_DATABASE	THE_WAREHOUSE
ACCOUNTADMIN	DEMO_DB	COMPUTE_WH

Back to ACCOUNTADMIN, and the database and warehouse came along unchanged.

Two refusals, worded very differently on purpose

Here is a role that exists, and that nobody ever granted to me.

Create

CREATE OR REPLACE ROLE RUNGRANTED_T158
    COMMENT = 'episode 158 - created and never granted to anybody';

Result1 row · 1 column

status
Role RUNGRANTED_T158 successfully created.

Use

USE ROLE RUNGRANTED_T158;

Query failed — Snowflake returned an error

003013 (42501): SQL access control error:
Requested role 'RUNGRANTED_T158' is not assigned to the executing user.  Specify another role to activate.

003013. It names the role and it tells me exactly why I cannot have it: the role is not assigned to the executing user.

Query

SELECT CURRENT_ROLE() AS THE_ROLE;

Result1 row · 1 column

THE_ROLE
ACCOUNTADMIN

And the session did not change roles on the way, which matters when you are halfway through a script.

Now the same statement, pointed at a role that does not exist at all.

Use

USE ROLE NO_SUCH_ROLE_T158;

Query failed — Snowflake returned an error

002043 (02000): SQL compilation error:
Object does not exist, or operation cannot be performed.

002043, object does not exist, or operation cannot be performed. Read that again, because it names nothing. No role name. Nothing.

Query

SELECT CURRENT_ROLE() AS THE_ROLE;

Result1 row · 1 column

THE_ROLE
ACCOUNTADMIN

One of those refusals tells you what you asked for. The other will not even confirm the name exists. That is deliberate: if it named missing roles, you could go fishing for the ones that are real.

Now three roles, stacked

Roles can hold other roles, so let's build a chain three deep and see what the catalogue makes of it.

Create

CREATE OR REPLACE ROLE RGRAND_T158 COMMENT = 'episode 158 - the bottom';

Result1 row · 1 column

status
Role RGRAND_T158 successfully created.

Create

CREATE OR REPLACE ROLE RCHILD_T158 COMMENT = 'episode 158 - the middle';

Result1 row · 1 column

status
Role RCHILD_T158 successfully created.

Create

CREATE OR REPLACE ROLE RPARENT_T158 COMMENT = 'episode 158 - the top';

Result1 row · 1 column

status
Role RPARENT_T158 successfully created.

Grant

GRANT ROLE RGRAND_T158 TO ROLE RCHILD_T158;

Result1 row · 1 column

status
Statement executed successfully.

Grant

GRANT ROLE RCHILD_T158 TO ROLE RPARENT_T158;

Result1 row · 1 column

status
Statement executed successfully.

Now let's ask what the parent has been given.

Query

SHOW GRANTS TO ROLE RPARENT_T158;

Result1 row · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by
2026-10-06 11:23:34.549000-07:00	USAGE	ROLE	RCHILD_T158	ROLE	RPARENT_T158	false	ACCOUNTADMIN

Query

SELECT "privilege", "granted_on", "name", "granted_to", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

privilege	granted_on	name	granted_to	grantee_name
USAGE	ROLE	RCHILD_T158	ROLE	RPARENT_T158

One row. Privilege USAGE, granted on ROLE, and the name is the child. Look at that wording, because it is a nice way to think about the whole subject: holding a role is itself a privilege.

And the child has the same shape, pointing at the grandchild.

Query

SHOW GRANTS TO ROLE RCHILD_T158;

Result1 row · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by
2026-10-06 11:23:34.177000-07:00	USAGE	ROLE	RGRAND_T158	ROLE	RCHILD_T158	false	ACCOUNTADMIN

Query

SELECT "privilege", "granted_on", "name", "granted_to", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

privilege	granted_on	name	granted_to	grantee_name
USAGE	ROLE	RGRAND_T158	ROLE	RCHILD_T158

So each role sees only what was granted directly to it. The parent's grid does not list the grandchild.

Asked the other way round, here is who holds the grandchild.

Query

SHOW GRANTS OF ROLE RGRAND_T158;

Result1 row · 5 columns

created_on	role	granted_to	grantee_name	granted_by
2026-10-06 11:23:34.177000-07:00	RGRAND_T158	ROLE	RCHILD_T158	ACCOUNTADMIN

Query

SELECT "role", "granted_to", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

role	granted_to	grantee_name
RGRAND_T158	ROLE	RCHILD_T158

granted_to is ROLE and the grantee is the child. That is the same shape as the user grant earlier, with a role in the grantee column instead.

And the two counting columns in SHOW ROLES make the whole tree readable at a glance.

Query

SHOW ROLES LIKE '%\\_T158';

Result5 rows · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-10-06 11:23:33.627000-07:00	RCHILD_T158	N	N	N	0	1	1	ACCOUNTADMIN	episode 158 - the middle	N
2026-10-06 11:23:33.342000-07:00	RGRAND_T158	N	N	N	0	1	0	ACCOUNTADMIN	episode 158 - the bottom	N
2026-10-06 11:23:33.892000-07:00	RPARENT_T158	N	N	N	0	0	1	ACCOUNTADMIN	episode 158 - the top	N
2026-10-06 11:23:32.214000-07:00	RUNGRANTED_T158	N	N	N	0	0	0	ACCOUNTADMIN	episode 158 - created and never granted to anybody	N
2026-10-06 11:23:26.856000-07:00	R_T158	N	N	N	1	0	0	ACCOUNTADMIN	episode 158 - a role with no grants	N

Query

SELECT "name", "granted_to_roles", "granted_roles", "assigned_to_users"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";

Result5 rows · 4 columns

name	granted_to_roles	granted_roles	assigned_to_users
RCHILD_T158	1	1	0
RGRAND_T158	1	0	0
RPARENT_T158	0	1	0
RUNGRANTED_T158	0	0	0
R_T158	0	0	1

The parent holds one and is held by nobody. The grandchild is held by one and holds nothing. The child is in the middle, one each way. That is the chain, in two columns.

So what does CURRENT_ROLE say when you wear the parent?

Let's grant the parent to ourselves and put it on.

Grant

-- The real user name is replaced with a stand-in name here.
GRANT ROLE RPARENT_T158 TO USER MY_USER_NAME;

Result1 row · 1 column

status
Statement executed successfully.

Use

USE ROLE RPARENT_T158;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE()             AS THE_ROLE,
       CURRENT_SECONDARY_ROLES()  AS THE_SECONDARY_ROLES,
       CURRENT_DATABASE()         AS THE_DATABASE;

Result1 row · 3 columns

THE_ROLE	THE_SECONDARY_ROLES	THE_DATABASE
RPARENT_T158	{"roles":"ACCOUNTADMIN,ORGADMIN,R_T158","value":"ALL"}	DEMO_DB

Query

SELECT CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 2 columns

THE_ROLE	THE_WAREHOUSE
RPARENT_T158	COMPUTE_WH

Just the parent. The two roles underneath it do not show up in CURRENT_ROLE() at all.

So inheritance is real, and it is invisible there. CURRENT_ROLE() only ever reports the one you put on.

Use

USE ROLE ACCOUNTADMIN;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE()      AS THE_ROLE,
       CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_WAREHOUSE() AS THE_WAREHOUSE;

Result1 row · 3 columns

THE_ROLE	THE_DATABASE	THE_WAREHOUSE
ACCOUNTADMIN	DEMO_DB	COMPUTE_WH

Can a role be granted to itself?

Let's try the short way round first.

Grant

GRANT ROLE R_T158 TO ROLE R_T158;

Query failed — Snowflake returned an error

003010 (42601): SQL execution error: Circular role grants not allowed.
Granting role R_T158 to R_T158 would create a cycle.

003010, circular role grants not allowed, and then it spells it out: granting that role to itself would create a cycle. It names the role twice and uses the word cycle. There is nothing to interpret there.

Now the longer way round. The parent already holds the child, and the child holds the grandchild, so granting the parent to the grandchild would close the loop.

Grant

GRANT ROLE RPARENT_T158 TO ROLE RGRAND_T158;

Query failed — Snowflake returned an error

003010 (42601): SQL execution error: Circular role grants not allowed.
Granting role RPARENT_T158 to RGRAND_T158 would create a cycle.

The same code, naming the pair. Snowflake walked the chain and found the problem rather than only checking the two names in front of it.

Query

SHOW GRANTS TO ROLE RGRAND_T158;

Result0 rows · 8 columns · scroll →

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by

That grid is empty, and the empty grid is the point. The grandchild still holds nothing, which is what it held before the refused grant. Nothing was half applied.

Which is worth knowing. A refused grant does not leave you in a state you have to go and tidy up.

Now secondary roles, and a grid that is not an answer

There is a second thing a session carries and it is easy to miss. As well as the role you put on, a session can have secondary roles switched on behind it.

So before we change anything, let's ask what they are right now.

Query

SELECT CURRENT_ROLE()            AS THE_ROLE,
       CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"RPARENT_T158,ORGADMIN,R_T158","value":"ALL"}

There is already a list in there. Three role names, and a value of ALL.

Now let's run the statement that switches them all on.

Use

USE SECONDARY ROLES ALL;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE()            AS THE_ROLE,
       CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"RPARENT_T158,ORGADMIN,R_T158","value":"ALL"}

The same value. Identical to the grid before it. Nothing changed.

What this pair of grids cannot show

So I have to be straight about that grid. It is not an answer. They were already all switched on when I asked, so this run cannot tell you what USE SECONDARY ROLES ALL does. A grid that did not change tells you nothing unless you knew the starting point, and here the starting point was already the destination.

What we can show is the other direction.

Use

USE SECONDARY ROLES NONE;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE()            AS THE_ROLE,
       CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"","value":""}

And now it is completely empty. Roles blank, value blank. USE SECONDARY ROLES NONE is a real answer, and the one above it is not. I would rather say that than let both look the same.

A few small ones worth having seen

Creating a role that already exists.

Create

CREATE ROLE R_T158;

Query failed — Snowflake returned an error

002002 (42710): SQL compilation error:
Object 'R_T158' already exists.

002002, already exists. Plain enough. Now the same thing with IF NOT EXISTS.

Create

CREATE ROLE IF NOT EXISTS R_T158;

Result1 row · 1 column

status
R_T158 already exists, statement succeeded.

Query

SHOW ROLES LIKE 'R\\_T158';

Result1 row · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-10-06 11:23:26.856000-07:00	R_T158	N	N	N	1	0	0	ACCOUNTADMIN	episode 158 - a role with no grants	N

Query

SELECT "name", "comment" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 2 columns

name	comment
R_T158	episode 158 - a role with no grants

Silent, and the original comment is still there. IF NOT EXISTS did not overwrite anything, the same way it did not for a warehouse three posts ago.

A role name in lower case, in double quotes, with spaces in the middle of it.

Create

CREATE OR REPLACE ROLE "lower role t158" COMMENT = 'episode 158 - quoted name';

Result1 row · 1 column

status
Role lower role t158 successfully created.

Query

SHOW ROLES LIKE 'lower role t158';

Result1 row · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-10-06 11:23:41.686000-07:00	lower role t158	N	N	N	0	0	0	ACCOUNTADMIN	episode 158 - quoted name	N

Accepted, and SHOW ROLES reports it with the spaces and the lower case exactly as written.

And granting a role that does not exist.

Grant

GRANT ROLE NO_SUCH_ROLE_T158 TO ROLE R_T158;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Role 'NO_SUCH_ROLE_T158' does not exist or not authorized. Your primary role ACCOUNTADMIN must have at least one privilege granted on ROLE NO_SUCH_ROLE_T158.

002003, and it names the role. Granting a role to a user that does not exist is the same code and names the user, but that message ends by naming the account's locator, so the refusal is not shown on this page.

Statement (result withheld)

-- Granting a role to a user that does not exist.
-- The refusal is 002003, and it is NOT shown on this page: the message ends
-- by naming the account locator, which is not something to publish.
GRANT ROLE R_T158 TO USER NO_SUCH_USER_T158;

Now the one that bites: dropping a role something hangs off

The parent role is granted to me and the child hangs off it. Can I drop it anyway?

First, who holds it. That grid names the account's user, so here is the statement without it.

Query (result withheld)

-- Who holds the parent role, before it is dropped.
-- The result is withheld: the grantee_name column is the account's user.
SHOW GRANTS OF ROLE RPARENT_T158;

One row, granted to a user. Now the drop.

Drop

DROP ROLE IF EXISTS RPARENT_T158;

Result1 row · 1 column

status
RPARENT_T158 successfully dropped.

Successfully dropped. No warning, no question, no mention of the user holding it or the role hanging off it.

Query

SHOW ROLES LIKE 'RPARENT\\_T158';

Result0 rows · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group

And it really is gone. But the child is still there, so who holds the child now?

Query

SHOW GRANTS OF ROLE RCHILD_T158;

Result0 rows · 5 columns

created_on	role	granted_to	grantee_name	granted_by

Query

SELECT "role", "granted_to", "grantee_name"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result0 rows · 3 columns

role	granted_to	grantee_name

Nobody. Two empty grids. Dropping the parent orphaned the child, and nothing told me that would happen.

What a drop does not tell you

The child role still exists, still holds the grandchild, and nobody can reach it any more. The only way to see that is to look afterwards. Before you drop a role, run SHOW GRANTS OF ROLE on it and see what is hanging off it.

Query

SELECT CURRENT_ROLE() AS THE_ROLE;

Result1 row · 1 column

THE_ROLE
ACCOUNTADMIN

And the session is unaffected, because the role we dropped was not the one we were wearing.

One last thing: let's put the account back

Take the first role back off myself, then drop everything this post made.

Revoke

-- The real user name is replaced with a stand-in name here.
REVOKE ROLE R_T158 FROM USER MY_USER_NAME;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW GRANTS OF ROLE R_T158;

Result0 rows · 5 columns

created_on	role	granted_to	grantee_name	granted_by

Nobody holds it now, and that grid has nothing in it to withhold.

Drop

DROP ROLE IF EXISTS R_T158;

Result1 row · 1 column

status
R_T158 successfully dropped.

Drop

DROP ROLE IF EXISTS RCHILD_T158;

Result1 row · 1 column

status
RCHILD_T158 successfully dropped.

Drop

DROP ROLE IF EXISTS RGRAND_T158;

Result1 row · 1 column

status
RGRAND_T158 successfully dropped.

Drop

DROP ROLE IF EXISTS "lower role t158";

Result1 row · 1 column

status
lower role t158 successfully dropped.

And now the checks.

Query

SHOW ROLES;

Result7 rows · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group
2026-09-21 18:33:41.930000-07:00	ACCOUNTADMIN	Y	Y	N	1	0	2		Account administrator can manage all aspects of the account.	N
2026-09-21 18:33:42.322000-07:00	ORGADMIN	N	N	N	1	0	0		Organization administrator can manage organizations and accounts in organizations	N
2026-09-21 18:33:41.959000-07:00	PUBLIC	N	N	Y	0	0	1		Public role is automatically available to every user in the account.	N
2026-09-21 18:33:41.947000-07:00	SECURITYADMIN	N	N	Y	0	1	1		Security administrator can manage security aspects of the account.	N
2026-09-21 18:33:47.878000-07:00	SNOWFLAKE_LEARNING_ROLE	N	N	Y	0	1	0	ACCOUNTADMIN	Created by Snowflake during account provisioning	N
2026-09-21 18:33:41.953000-07:00	SYSADMIN	N	N	Y	0	1	0		System administrator can create and manage databases and warehouses.	N
2026-09-21 18:33:41.941000-07:00	USERADMIN	N	N	Y	0	1	0		User administrator can create and manage users and roles	N

Seven roles, the same seven the account started with. Compare that grid with the first one in this post.

Query

SHOW ROLES LIKE '%\\_T158';

Result0 rows · 11 columns · scroll →

created_on	name	is_default	is_current	is_inherited	assigned_to_users	granted_to_roles	granted_roles	owner	comment	is_from_organization_user_group

And nothing whose name ends in _T158 is left.

The capture's closing check also asks CURRENT_USER(), so its grid is withheld as well. Here is the statement.

Query (result withheld)

-- The closing check.
-- The result is withheld: this grid names the account's user.
SELECT CURRENT_USER()            AS THE_USER,
       CURRENT_ROLE()            AS THE_ROLE,
       CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES,
       CURRENT_DATABASE()        AS THE_DATABASE,
       CURRENT_SCHEMA()          AS THE_SCHEMA,
       CURRENT_WAREHOUSE()       AS THE_WAREHOUSE;

It reported secondary roles empty, the role back to ACCOUNTADMIN, and the same database, schema and warehouse the post started on.

What this post did not touch

No privilege is granted to a built-in role anywhere in this post, nothing is revoked that the post did not grant first, and no object privilege appears at all. Every grant here is role to role, or role to user. The object privileges start in the next post.

Key points

• A role is a name that holds privileges, and a brand new one holds none.
• Nothing on screen tells you that. The CURRENT_ functions report the session, not the role, so they all answer happily.
• SHOW GRANTS TO ROLE is what a role can do. SHOW GRANTS OF ROLE is who holds it.
• Wearing a role you were never granted is 003013, and it names the role. Wearing one that does not exist is 002043, and it names nothing at all.
• A role granted to a role shows up as privilege USAGE on a ROLE. Holding a role is itself a privilege.
• granted_to_roles and granted_roles in SHOW ROLES read a role hierarchy at a glance.
• CURRENT_ROLE() reports only the role you put on, never the ones it inherits.
• Cycles are refused both ways with 003010, and a refused grant leaves nothing to tidy up.
• USE SECONDARY ROLES NONE empties the list. The ALL run in this post proves nothing, because they were already all on.
• CREATE ROLE IF NOT EXISTS keeps the original comment. A quoted lower-case name with spaces is legal.
• Dropping a role that another role hangs off succeeds silently and leaves that other role held by nobody.

Next in this series

In the next post we grant the privileges themselves: GRANT and REVOKE, one privilege at a time, with a different refusal at every step, until a SELECT finally runs.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.36.101) running the demo script for this episode. Seven grids are withheld because they name the account's user or its account locator, and the builder cannot leave one column out of a grid; each of those statements is on the page with its result left off, and nothing about the withheld values is described. Three statements are shown with a stand-in name in place of the user name. Every role created here is dropped here, and no privilege is granted to a built-in role. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake access control documentation.