Default value of DEFAULT_SECONDARY_ROLES object property on users changed to (‘ALL’)¶

This behavior change was originally introduced in the 2024_07 bundle. To give users additional time to evaluate the change, this behavior change remains disabled in 2024_08.

This BCR affects all users. If a new or existing user has their DEFAULT_SECONDARY_ROLES object property unset, or set to NULL, then their DEFAULT_SECONDARY_ROLES object property changes to ('ALL').

If a new or existing user has their DEFAULT_SECONDARY_ROLES object property explicitly set, then their DEFAULT_SECONDARY_ROLES object property does not change.

Setting a user’s DEFAULT_SECONDARY_ROLES object property to () specifies that a user does not have secondary roles. If you want to preserve the existing behavior of the DEFAULT_SECONDARY_ROLES object property in your account, you can use the following procedure to explicitly set DEFAULT_SECONDARY_ROLES to an empty list:

CREATE OR REPLACE PROCEDURE update_default_secondary_roles()
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
let updated_users = [];
let users = snowflake.execute({sqlText: "SHOW USERS"});
while (users.next()) {
  let username = users.getColumnValue("name");
  let dsr = users.getColumnValue("default_secondary_roles");
  if (dsr !== "") {
    continue;
  }
  snowflake.execute({
    sqlText: "alter user identifier(?) set default_secondary_roles=()",
    binds: ["\"" + username + "\""],
  });
  updated_users.push(username);
}
return updated_users;
$$;

CALL update_default_secondary_roles();

For more information, see the community article

Before the change:

The default value of the DEFAULT_SECONDARY_ROLES object property on users is NULL.

After the change:

The default value of the DEFAULT_SECONDARY_ROLES object property on users is (‘ALL’).

Ref: 1692