Search code examples
databasepermissionssnowflake-cloud-data-platformsql-grant

create db from share from different role in reader account Snowflake

I have a share(i.e. shareTest_share) in accountAdmin role in one of my reader accounts in snowflake.

Now I want to create a database from this, but from sysAdmin role.

The statement for creating database is:

CREATE or replace DATABASE shareTest_db FROM SHARE mukulanalytics.shareTest_share

This is giving permission issue since I am trying to execute it from the sysAdmin role instead of accountAdmin role which has the access to shareTest_share share.

Error:

SQL access control error: Insufficient privileges to operate on foreign share 'SHARETEST_SHARE'

How can I create a database from share in a different role with share in another role?

Solution

  • You will have to grant the IMPORT SHARE privilege to SYSADMIN/any other users

    use role accountadmin

    grant import share on account to sysadmin

    use role sysadmin

    create database TEST from share <>

    https://docs.snowflake.com/en/user-guide/security-access-privileges-shares.html