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;