#sqltip #powershelltip #dostip #checkport.powershell.tnc <hostname> -port <num>.e.g. tnc winpc1 -port445."WARNING: TCP Connect to winpc1:445 failed".

#DOSTip #BatchScript #ErrorLogging. set "Var1=abc<def<ghi".echo %Var1%.abc<def<ghi. @rem-- Redirect output to file, and error messages.echo Test1 > logfile.out 2>&1.

#sqltip #snowflaketip #loginerrors. USE DATABASE SNOWFLAKE;. SELECT *.FROM ACCOUNT_USAGE.LOGIN_HISTORY.WHERE USER_NAME = 'username'.AND EVENT_TIMESTAMP > DATEADD('day', -1, CURRENT_DATE).ORDER BY EVENT_TIMESTAMP DESC.LIMIT 100;.

-- create a new user.SHOW ROLES;.USE ROLE ACCOUNTADMIN;.-- USE ROLE SECURITYADMIN;.CREATE USER {usr} PASSWORD = '{pw}' MUST_CHANGE_PASSWORD = TRUE;. SHOW GRANTS TO ROLE {role};.GRANT USAGE ON WAREHOUSE {warehouse} TO ROLE {role};.GRANT USAGE ON DATABASE {database} TO ROLE {role};.

create role r_admin.grant create warehouse on account to role r_admin;.grant usage on database t_db to role r_admin;. show grants on role r_admin;.grant role r_admin to user <new_user>;.show grants on user <new_user>;.

#sqltip #snowflaketip #Role #SFJJSON.SELECT CURRENT_AVAILABLE_ROLES();. SELECT "SEQ","KEY","PATH","INDEX","VALUE","THIS".FROM TABLE.( FLATTEN. (INPUT => PARSE_JSON( . CURRENT_AVAILABLE_ROLES() ). ).).WHERE "VALUE" LIKE '%<dbname>%' ;.

SHOW GRANTS ON WAREHOUSE T_WH_XS ;. grant MODIFY on WAREHOUSE T_WH_XS to role T_ROLE_OWNER ;. grant MONITOR on WAREHOUSE T_WH_XS to role <roles> ;.grant OPERATE on WAREHOUSE T_WH_XS to role <roles> ;.grant USAGE on WAREHOUSE T_WH_XS to role <roles> ;.

SHOW GRANTS ON DATABASE T_DB ;. grant usage on DATABASE T_DB TO ROLE <roles>;. grant create schema on . to tole T_ROLE_OWNER ;.grant modify on . to role T_ROLE_OWNER ;.

. grant usage,monitor on database T_DB to role T_ROLE;.grant usage on warehouse TEST_WH to role T_ROLE;.grant usage on schema T_DB .T_SCH to role T_ROLE;.grant select on all tables in database T_DB to role T_ROLE;.grant select on future tables in database T_DB to role T_ROLE;.

#sqltip #snowflaketip .-- setup new TEST db. create database TEST_DB;.create or replace role TEST_ROLE;.create schema TEST_SCH;. /1.

-- Add base role definitions.create or replace role dev_db_ro;.create or replace role dev_db_rw;.create or replace role dev_db_exec;. grant usage,monitor on database <dev_db> to role RRR;.grant usage on warehouse <WH> to role RRR;.

. FROM ( SELECT distinct procedure_schema .from dev_db.INFORMATION_SCHEMA.procedures.WHERE procedure_owner != 'dev_db_rw' ).

. FROM ( SELECT distinct stage_schema .from dev_db.INFORMATION_SCHEMA.stages.WHERE stage_owner != 'dev_db_rw' ).

SELECT 'grant. in schema' || ' dev_db ' || '.' || sch_name || ' to role ' || 'dev_db_ro'.FROM ( SELECT distinct(SCHEMA_NAME), CATALOG_NAME.from dev_db.INFORMATION_SCHEMA.SCHEMATA .WHERE schema_name != 'INFORMATION_SCHEMA'.ORDER BY schema_name) ;.

. grant usage on all future functions in schema dev_db.FFF to role dev_db_exec ;. alter database dev_db set default_ddl_coalition = 'en-ci';.--check params.show parameters in database dev_db;. /26.

. grant usage on database dev_db to role dev_db_exec ;.grant usage on all schemas in database dev_db to role dev_db_exec ;.grant usage on all functions in schema dev_db.FFF to role dev_db_exec ;. /25.

. grant usage on all procedures in schema dev_db.PPP to role dev_db_exec ;.grant usage on all future procedures in schema dev_db.PPP to role dev_db_exec ;.grant usage on future functions in database dev_db to role dev_db_exec ;. /24.

-- Grant exec roles to dev_db.grant usage on database dev_db to role dev_db_exec ;.grant usage on future procedures in database dev_db to role dev_db_exec ;.grant usage on all schemas in database dev_db to role dev_db_exec ;. /23.

. grant usage on future file formats in database dev_db to role dev_db_rw ;. /22.

. grant update on future tables in database dev_db to role dev_db_rw ;.grant delete on future tables in database dev_db to role dev_db_rw ;.grant usage on future stages in database dev_db to role dev_db_rw ;. /21.