0
0
During this post we will discuss about Transferring Pipe Ownership. In our current Account majority of PIPES are owned by ACCOUNTADMIN role. As part of maintenance, this becomes a painful task for AccountAdmin role. As per discussion with Business, it was decided to transfer some responsibility from ADMIN to other Role. After the mutual agreement it was confirm that we would transfer the PIPE ownership from ACCOUNTADMIN role to new Role.
As per below screenshot, we see SALES PIPE is available in DEMO_DB database own by ACCOUNTADMIN Role
Transferring the PIPE ownership requires the following steps to be perform.
- SET PIPE_EXECUTION_PAUSED = TRUE : It is must Pipe to be in paused state while transferring ownership of the pipe to another role. Transferring the ownership without Pausing PIPE will generate the following error.
ALTER PIPE SALES SET PIPE_EXECUTION_PAUSED = TRUE;
- Transfer ownership of the pipe using GRANT OWNERSHIP.
- Verify the Owner of PIPE, it has been change to SUPER_ROLE.
PIPE Ownership change
- Login with the SUPER_ROLE and Resume the pipe using SYSTEM$PIPE_FORCE_RESUME.
- Now Verify the status of PIPE, it is in RUNNING state.
- Try to upload the file into destination directory which invokes SQS notification and execute the PIPE.
- Verify the data in CUST_SALES table which gets upload by SNOWPIPE.
NOTE:
You may face the below issue during the Snowpipe execution. The probable reason for failure is STAGE and FILE FORMAT use in PIPE Copy definition are not grant to the SUPER_ROLE. So please ensure during the PIPE OWNERSHIP transfer, dependencies object (Table, Stage, File Format) to be grant to the new Role.
