Clone Stored Procedure
1 0
Read Time:2 Minute, 12 Second

During this post, we are going to discuss how the object privileges works during the CLONE operation. Recently one of my colleagues encountered a scenario a scenario where they had cloned few tables from one DB to Target DB. While cloning they assumed that the object privileges would also get clone or copied to the target DB. But they were surprised to find that only the objects were cloned and there were no privileges copied on objects. Only the object OWNERSHIP was there in target DB and the rest were not moved or copied.

The challenge with object privileges during cloning becomes critical, especially when custom or authorized roles face access issues with the cloned objects. One potential solution is to query the source database, exporting a list of all privileges for the corresponding cloned objects. However, the complexity arises when dealing with numerous tables, each having multiple privileges assign to various custom roles. To address this, a dynamic process needs to be develop. This process will generate GRANT commands dynamically, facilitating the assignment of privileges in the target database.

Hence, I have developed the below procedure in my target DB which extracts all the privileges from the source and assigns them to the object that resides in the target DB.

Technical Implementation:

Source DB:

Below is the screenshot of Source DB i.e., DEMO_DB with the list of tables and respective Privileges.

We can see the objects are assigned to REP and FLM roles respectively.

Source DB Objects

Target DB:

We can verify the list of objects CLONE to Target DB and their respective privileges. We can clearly see that only OWNERSHIP privileges are there.

Target DB Privileges

Below is the Procedure to implement the logic.

Clone Stored Procedure

Execute the Procedure and verify the output.

Proc output

Now check if Privileges are Clone to the Target DB or not.

Target DB Privileges


Snowflake has provided COPY GRANTS command to copy the object and their respective privileges. Using COPY GRANTS with command will copies the objects and their respective privileges.

So I created new database and clone the table from DEMO_DB with COPY_GRANTS

create database DB_CLONE1;


This has copied the entire privileges to target DB.

COPY Grants

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

Leave a Reply

Your email address will not be published. Required fields are marked *