Snowflake Data Cloud

I had a classic task to clone a user in Snowflake, where we have already established Single Sign On.

I needed to create a new user for SSO, that is based on an existing users default settings and current roles.

WITH MyCTE
AS
(SELECT 'Chris' AS FirstName,'Jacobsen' As LastName, 'JacobsenC1' As UserName) 
,UserData AS ( SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.USERS WHERE name = 'YEEA' )
,RoleData AS (SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS WHERE grantee_name = 'YEEA' AND DELETED_ON IS NULL)

SELECT 
CONCAT('CREATE USER IF NOT EXISTS JacobsenC1 ',
'  LOGIN_NAME = ''JacobsenC1@mydomain.com'' ' ,
'  FIRST_NAME   = ''',dt.FirstName,''' ' ,
'  LAST_NAME    = ''',dt.LastName,''' ' ,
'  DISPLAY_NAME = ''',dt.FirstName,' ',dt.LastName,''' ',
'  EMAIL        = ''',dt.UserName,'@mydomain.com',''' ' ,
'  DEFAULT_ROLE = ''',t1.DEFAULT_ROLE,''' ' ,
'  DEFAULT_NAMESPACE = ''',t1.DEFAULT_NAMESPACE ,''' ' ,
'  DEFAULT_WAREHOUSE = ''',t1.DEFAULT_WAREHOUSE ,''';') AS cmd
FROM UserData t1
CROSS JOIN MyCTE AS dt
UNION ALL
SELECT CONCAT('GRANT ROLE ',t2.Role,' TO USER ',dt.UserName,';')
FROM RoleData t2
CROSS JOIN MyCTE AS dt;