Search Optimization: Removing Search Optimization from a Table Requires the ADD SEARCH OPTIMIZATION Privilege¶

According to What Access Control Privileges Are Needed For the Search Optimization Service, making changes to a table’s search optimization configuration requires not only ownership of the table, but also the ADD SEARCH OPTIMIZATION access control privilege on the schema that contains the table.

However, currently, you can remove search optimization from a table you own without having the ADD SEARCH OPTIMIZATION privilege.

Snowflake requires the ADD SEARCH OPTIMIZATION privilege to behave as documented:

Previously:

The command ALTER TABLE DROP SEARCH OPTIMIZATION succeeds when used on a table you own, even if your role does not have the ADD SEARCH OPTIMIZATION privilege on the schema that contains the table.

Currently:

The ALTER TABLE DROP SEARCH OPTIMIZATION command fails when used on a table you own if your role does not have the ADD SEARCH OPTIMIZATION privilege. The error message is as follows:

FAILURE: SQL access control error:
Insufficient privileges to operate on schema '<schema_name>'

Copy

If you have this privilege, the command succeeds.

To grant the required privilege to a role, issue the following command:

GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>;

Ref: 1046