Robert Ensinger

Sangeet's 'Login As' for V13 MSSQL

Discussion created by Robert Ensinger on Jul 20, 2012
Latest reply on Oct 7, 2015 by Robert Ensinger
Hi all.
I want to post this here for future retrieval and to help the next person. If you're a fan of Sangeet's 'Login As' customization, here's the V13 code for MSSQL.

Note: This is a customization that will not be supported by CA.
 <statement id="security.getUser_stmt">
    <sql>
      <text>
        select  
        us.id
        , us.user_name
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN (SELECT pwd from cmn_sec_users WHERE user_name = 'admin') ELSE us.pwd END) pwd
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.force_pwd_change END) force_pwd_change
        , us.first_name
        , us.last_name
        , us.email_address
        , us.locale
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.is_ldap END ) is_ldap
        , us.timezone
        , us.last_pwd_change
        , us.bad_login_count
        , l.language_code
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN  'ACTIVE' ELSE lu.lookup_code END ) user_status
        , r.id resource_id
        , r.unique_name
        , us.home_url
        , us.nav_state
        , fm.id favorites_menu_id
        , us.screen_reader_opt
        , us.high_contrast_ui
        from    cmn_sec_users us left outer join
                    cmn_menu_items fm on  fm.principal_id = us.id
                                      and fm.principal_type = ?
                                      and fm.container_type_code = ?
                                      and fm.parent_menu_id is null,
                cmn_languages l, cmn_lookups lu,
                srm_resources r
        where   us.language_id = l.id
        and     r.user_id = us.id
        and     us.user_status_id = lu.id
        and
      </text>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" constantValue="USER"/>
      <param type="string" constantValue="MENU"/>
    </sql>
    <sql dbVendor="mssql">
      <text>
        UPPER(US.USER_NAME) = Replace(?,'ADMIN/','')
      </text>
      <param type="string" name="userName"/>
    </sql>
    <sql dbVendor="oracle">
      <text>
        @UPPER@(US.USER_NAME)=@UPPER@(?)
      </text>
      <param type="string" name="userName"/>
    </sql>
    <outputMapping collectionElementName="user"/>
  </statement>

Outcomes