Script: Create Snowflake User

1min
There is a need to create a Snowflake user for DataLakeHouse.io to be able to write to the raw landing zone where the data from your source(s) will be synchronized/replicated.
Please use our community support or enterprise support for any assistance or questions related to security permissions or other protocols related to Snowflake administration if needed.
Use the script below by replacing the values in the brackets per your environment then run the script on your Snowflake account. Once the script has been executed you can use the credentials and other information in the DataLakeHouse.io Snowflake connector form to create the connection.  The user running the script should typically have ACCOUNTADMIN role access, or both SYSADMIN and SECURITYADMIN roles privileges.
SQL
-- // DataLakeHouse.io Create User Script
  begin;


    -- // Create variables at top to propogate through script for user
    set role_name = 'DATALAKEHOUSE_ROLE';
    set user_name = 'DATALAKEHOUSE_USER';
    set user_password = 'tmp!password'; -- change to a unique password
    set warehouse_name = 'DATALAKEHOUSE_WH';
    set database_name = 'DATALAKEHOUSE_RAW';


    -- // Change role to securityadmin as required for user and role controls
    use role securityadmin;


    -- // Create role for DataLakeHouse.io with required grant
    create role if not exists identifier($role_name);
    grant role identifier($role_name) to role SYSADMIN;


    -- // Create a new user for DataLakeHouse.io interaction
    create user if not exists identifier($user_name)
    password = $user_password
    default_role = $role_name
    default_warehouse = $warehouse_name
    comment = 'DataLakeHouse.io user to synchronize data from our source systems into our Snowflake data cloud.';


    -- // Grant the created datalakehouse_role to the user
    grant role identifier($role_name) to user identifier($user_name);


    -- // Change the active role to sysadmin as required for db and wh controls
    use role sysadmin;


    -- // Create a warehouse for DataLakeHouse.io separate compute
    create warehouse if not exists identifier($warehouse_name)
    warehouse_size = xsmall
    warehouse_type = standard
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true
    comment = 'DataLakeHouse.io Warehouse to separate compute from other warehouse usage in our Snowflake account.';


    -- // Create the database for DataLakeHouse.io raw landing zone
    create database if not exists identifier($database_name);


    -- // Grant the DataLakeHouse.io role access to the created warehouse
    grant USAGE on warehouse identifier($warehouse_name)
    to role identifier($role_name);


    -- // Grant the DataLakeHouse.io access to database
    grant CREATE SCHEMA, MONITOR, USAGE on database identifier($database_name)
    to role identifier($role_name);


  --// Finally commit the transaction of creating the above objects
  commit;
-- // DataLakeHouse.io Create User Script
  begin;


    -- // Create variables at top to propogate through script for user
    set role_name = 'DATALAKEHOUSE_ROLE';
    set user_name = 'DATALAKEHOUSE_USER';
    set user_password = 'tmp!password'; -- change to a unique password
    set warehouse_name = 'DATALAKEHOUSE_WH';
    set database_name = 'DATALAKEHOUSE_RAW';


    -- // Change role to securityadmin as required for user and role controls
    use role securityadmin;


    -- // Create role for DataLakeHouse.io with required grant
    create role if not exists identifier($role_name);
    grant role identifier($role_name) to role SYSADMIN;


    -- // Create a new user for DataLakeHouse.io interaction
    create user if not exists identifier($user_name)
    password = $user_password
    default_role = $role_name
    default_warehouse = $warehouse_name
    comment = 'DataLakeHouse.io user to synchronize data from our source systems into our Snowflake data cloud.';


    -- // Grant the created datalakehouse_role to the user
    grant role identifier($role_name) to user identifier($user_name);


    -- // Change the active role to sysadmin as required for db and wh controls
    use role sysadmin;


    -- // Create a warehouse for DataLakeHouse.io separate compute
    create warehouse if not exists identifier($warehouse_name)
    warehouse_size = xsmall
    warehouse_type = standard
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true
    comment = 'DataLakeHouse.io Warehouse to separate compute from other warehouse usage in our Snowflake account.';


    -- // Create the database for DataLakeHouse.io raw landing zone
    create database if not exists identifier($database_name);


    -- // Grant the DataLakeHouse.io role access to the created warehouse
    grant USAGE on warehouse identifier($warehouse_name)
    to role identifier($role_name);


    -- // Grant the DataLakeHouse.io access to database
    grant CREATE SCHEMA, MONITOR, USAGE on database identifier($database_name)
    to role identifier($role_name);


  --// Finally commit the transaction of creating the above objects
  commit;
﻿
﻿
Updated 03 Mar 2023
Did this page help you?
Key Pair Authentication Setup
Change Log & Notes (Snowflake)