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 = ”[email protected]” ‘ , ‘ 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;

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 = ''[email protected]'' ' ,
'  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;