#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