Skip to content

Instantly share code, notes, and snippets.

@gmirsky

gmirsky/snowflake_reverse_engineer_user_ddl.sql

Last active September 1, 2021 13:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save gmirsky/ca38b9803205dfd75b06316afb701ba8 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save gmirsky/ca38b9803205dfd75b06316afb701ba8 to your computer and use it in GitHub Desktop.
Download ZIP
snowflake_reverse_engineer_user_ddl.sql
Raw
snowflake_reverse_engineer_user_ddl.sql
use role accountadmin;
--
show users;
--
CREATE temporary TABLE temp_users as SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
--
select 'CREATE USER ' || t."name" || '\n' ||
' PASSWORD=' || '''Ch@ng3MeN0W!''' || '\n' ||
' COMMENT = ' || '''' ||
case
when t."comment" is null then ''
else t."comment"
end || '''' || '\n' ||
' LOGIN_NAME=' || '''' ||
case
when t."login_name" is null then ''
else t."login_name"
end || '''' || '\n' ||
' DISPLAY_NAME=' || '''' ||
case
when t."display_name" is null then ''
else t."display_name"
end || '''' || '\n' ||
' FIRST_NAME=' || '''' ||
case
when t."first_name" is null then ''
else t."first_name"
end || '''' || '\n' ||
' LAST_NAME=' || '''' ||
case
when t."last_name" is null then ''
else t."last_name"
end || '''' || '\n' ||
' EMAIL=' || '''' ||
case
when t."email" is null then ''
else t."email"
end || '''' || '\n' ||
' DEFAULT_ROLE=' || '''' ||
case
when t."default_role" is null then ''
else t."default_role"
end || '''' || '\n' ||
' DEFAULT_WAREHOUSE=' || '''' || ' ' ||
case
when t."default_warehouse" is null then ''
else t."default_warehouse"
end || '''' || '\n' ||
' DEFAULT_NAMESPACE=' || '''' || ' ' ||
case
when t."default_namespace" is null then ''
else t."default_namespace"
end || '''' || '\n' ||
' MUST_CHANGE_PASSWORD = TRUE;'
user_ddl
from temp_users t;
--
drop table temp_users;
Raw
snowflake_reverse_engineer_user_ddl.sql.sql
use role accountadmin;
show users;
CREATE temporary TABLE temp_users as SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
describe table temp_users;
select * from temp_users;
select a."name",
a."created_on",
a."login_name",
a."display_name",
a."first_name",
a."last_name",
a."email",
a."mins_to_unlock",
a."days_to_expiry",
a."comment",
a."disabled",
a."must_change_password",
a."snowflake_lock",
a."default_warehouse",
a."default_namespace",
a."default_role",
a."default_secondary_roles",
a."ext_authn_duo",
a."ext_authn_uid",
a."mins_to_bypass_mfa",
a."owner",
a."last_success_login",
a."expires_at_time",
a."locked_until_time",
a."has_password",
a."has_rsa_public_key"
from temp_users a;
select 'CREATE USER ' || t."name" || '\n' ||
' PASSWORD=' || '''Ch@ng3MeN0W!''' || '\n' ||
' COMMENT = ' || '''' ||
case
when t."comment" is null then ''
else t."comment"
end || '''' || '\n' ||
' LOGIN_NAME=' || '''' ||
case
when t."login_name" is null then ''
else t."login_name"
end || '''' || '\n' ||
' DISPLAY_NAME=' || '''' ||
case
when t."display_name" is null then ''
else t."display_name"
end || '''' || '\n' ||
' FIRST_NAME=' || '''' ||
case
when t."first_name" is null then ''
else t."first_name"
end || '''' || '\n' ||
' LAST_NAME=' || '''' ||
case
when t."last_name" is null then ''
else t."last_name"
end || '''' || '\n' ||
' EMAIL=' || '''' ||
case
when t."email" is null then ''
else t."email"
end || '''' || '\n' ||
' DEFAULT_ROLE=' || '''' ||
case
when t."default_role" is null then ''
else t."default_role"
end || '''' || '\n' ||
' DEFAULT_WAREHOUSE=' || '''' || ' ' ||
case
when t."default_warehouse" is null then ''
else t."default_warehouse"
end || '''' || '\n' ||
' DEFAULT_NAMESPACE=' || '''' || ' ' ||
case
when t."default_namespace" is null then ''
else t."default_namespace"
end || '''' || '\n' ||
' MUST_CHANGE_PASSWORD = TRUE;'
user_ddl
from temp_users t;
drop table temp_users;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment