DROP EXTERNAL CONSUMER¶

Removes an external consumer from the account. You can’t drop an external consumer while any listing targets it.

See also:: CREATE EXTERNAL CONSUMER , ALTER EXTERNAL CONSUMER , SHOW EXTERNAL CONSUMERS , DESCRIBE EXTERNAL CONSUMER

Syntax¶

DROP EXTERNAL CONSUMER [ IF EXISTS ] <name>

Parameters¶

IF EXISTS: Drops the external consumer only if one with the specified name exists. If no external consumer with that name exists, the command does nothing and completes successfully instead of returning an error.
name: Specifies the identifier for the external consumer to drop.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Object	Notes
OWNERSHIP	External Consumer	Required to drop the external consumer.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

You can’t drop an external consumer while any listing targets it in external_targets.access.external_consumers. This applies to published and unpublished listings. The command fails with an error that names the listings, for example:
```
Cannot drop external consumer 'ACME_CONSUMER' because it is targeted by active listing(s), please retarget or drop those listings then retry. Listings (showing 1 of 1): ACME_LISTING
```
To drop the external consumer, first do one of the following for each listing named in the error:
- Remove the external consumer from the listing’s external_targets by using ALTER LISTING.
- Drop the listing by using DROP LISTING. You must unpublish a published listing before you can drop it.
All programmatic access tokens (PATs) and workload identities associated with the external consumer are dropped when the consumer is dropped.
Dropped external consumers can’t be recovered.

When the IF EXISTS clause is specified and the target object doesn’t exist, the command completes successfully without returning an error.

Examples¶

Drop an external consumer:

DROP EXTERNAL CONSUMER acme_consumer;

Drop an external consumer if it exists:

DROP EXTERNAL CONSUMER IF EXISTS acme_consumer;

Remove an external consumer from a listing, and then drop it. In this example, acme_listing targets acme_consumer and other_consumer. The listing is updated to target only other_consumer:

ALTER LISTING acme_listing AS
$$
title: "Acme listing"
description: "Shared with external consumers"
open_sharing:
  catalog_identifier: "acme-listing-catalog"
listing_terms:
  type: "OFFLINE"
external_targets:
  access:
    - external_consumers: [OTHER_CONSUMER]
$$;

DROP EXTERNAL CONSUMER acme_consumer;