Microstrategy Check for Users

/***************************************************************************

MSTR CHECK USER

*****************************************************************************/

select g.OBJECT_NAME as Groups, u.OBJECT_NAME as Users, u.ABBREVIATION, u.DESCRIPTION

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableNew";'

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableUser";'

     , 'REMOVE USER "'+ cast(u.ABBREVIATION as varchar) +'" FROM GROUP "_DisableNew";'

 from DSSMDOBJDEPN d

 join DSSMDOBJINFO u

   on d.OBJECT_ID = u.OBJECT_ID

 join DSSMDOBJINFO g

   on d.DEPN_OBJID = g.OBJECT_ID

where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

  and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

  and g.OBJECT_NAME LIKE 'MSTRProjectName%'--IN ('_Enable')

  and u.ABBREVIATION NOT IN (

select u.ABBREVIATION

  from DSSMDOBJDEPN d

  join DSSMDOBJINFO u

    on d.OBJECT_ID = u.OBJECT_ID

  join DSSMDOBJINFO g

    on d.DEPN_OBJID = g.OBJECT_ID

 where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

   and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

   and g.OBJECT_NAME IN ('_Enable'))

   AND u.ABBREVIATION NOT IN

     ( select u.ABBREVIATION

         from DSSMDOBJDEPN d

         join DSSMDOBJINFO u

           on d.OBJECT_ID = u.OBJECT_ID

         join DSSMDOBJINFO g

           on d.DEPN_OBJID = g.OBJECT_ID

        where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

          and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

          and g.OBJECT_NAME NOT LIKE 'MSTRProjectName%'

          and g.OBJECT_NAME NOT IN ('Everyone') 

      )

order by 4

-- ALTER USERS IN USER GROUP ' _DisableUser1' DISABLED;

select g.OBJECT_NAME as Groups, u.OBJECT_NAME as Users, u.ABBREVIATION, u.DESCRIPTION

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableUser1";'

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableUser";'

     , 'REMOVE USER "'+ cast(u.ABBREVIATION as varchar) +'" FROM GROUP "_DisableUser1";'

  from DSSMDOBJDEPN d

  join DSSMDOBJINFO u

    on d.OBJECT_ID = u.OBJECT_ID

  join DSSMDOBJINFO g

    on d.DEPN_OBJID = g.OBJECT_ID

 where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

   and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

   and g.OBJECT_NAME NOT IN ('_DisableUser1')

   and g.OBJECT_NAME IN ('Everyone')

   and u.ABBREVIATION in ( 'Abbreviation')

   and u.DESCRIPTION not in ('Description')

order by 4

-- ALTER USERS IN USER GROUP ' _DisableUser1' DISABLED;