Table V_USERS_AND_GROUPS

Users and Groups convenience view; provides all user groups regardless of whether or not users belong to them or not.

The underlying SELECT statement behind this view can be found further below the following table.

Column Name	Data Type	Description
CMSID	varchar	The actualy name of the CMS host machine without the port number, if used. В This is used in conjunction with OBJECTID or CUID to uniquely identify an object across environments
DATA_CATEGORY	varchar	The type of object the row is returning
ISCONCURRENTUSER	varchar	Is the user setup as a concurrent user? (0 = no, 1 = yes)
ISENABLED	varchar	Is the user enabled? (0 = no, 1 = yes)
ISNAMEDUSER	varchar	Is the user setup as a named user? (0 = no, 1 = yes)
ISPASSWORDCHANGEALLOWED	varchar	See USERGROUPS table
USERGROUPS_CREATED	datetime	See USERGROUPS table
USERGROUPS_CUID	varchar	See USERGROUPS table
USERGROUPS_DESCRIPTION	varchar	See USERGROUPS table
USERGROUPS_ID	int	See USERGROUPS table
USERGROUPS_NAME	nvarchar	See USERGROUPS table
USERGROUPS_OBJECTID	int	See USERGROUPS table
USERGROUPS_UPDATED	datetime	See USERGROUPS table
USERS_CREATED	datetime	See USERS table
USERS_CUID	varchar	See USERS table
USERS_DESCRIPTION	varchar	See USERS table
USERS_EMAILADDRESS	varchar	See USERS table
USERS_FULLNAME	varchar	See USERS table
USERS_ID	int	See USERS table
USERS_ISDELETED	bit	See USERS table
USERS_LASTLOGONTIME	datetime	See USERS table
USERS_NAME	nvarchar	See USERS table
USERS_OBJECTID	int	See USERS table
USERS_UPDATED	datetime	See USERS table

(Note: This example illustrates the SQL Server version of the V_USERS_AND_GROUPS view)

CREATE VIEW [V_USERS_AND_GROUPS] as

SELECT

USERGROUPS.CMSID,

'USERS_AND_GROUPS' as "DATA_CATEGORY",

USERGROUPS.ID as "USERGROUPS_ID",

USERGROUPS.OBJECTID as "USERGROUPS_OBJECTID",

USERGROUPS.CUID as "USERGROUPS_CUID",

USERGROUPS.NAME as "USERGROUPS_NAME",

USERGROUPS.DESCRIPTION as "USERGROUPS_DESCRIPTION",

USERGROUPS.CREATED as "USERGROUPS_CREATED",

USERGROUPS.UPDATED as "USERGROUPS_UPDATED",

USERS.OBJECTID as "USERS_OBJECTID",

USERS.CUID as "USERS_CUID",

USERS.ID as "USERS_ID",

USERS.NAME as "USERS_NAME",

USERS.FULLNAME as "USERS_FULLNAME",

CASE WHEN USERS.ISPASSWORDCHANGEALLOWED = 0 then 'NO' else 'YES' END as "ISPASSWORDCHANGEALLOWED",

CASE WHEN USERS.ISCONCURRENTUSER = 0 then 'NO' else 'YES' END as ISCONCURRENTUSER,

CASE WHEN USERS.ISNAMEDUSER = 0 then 'NO' else 'YES' END as ISNAMEDUSER,

CASE WHEN USERS.ISENABLED = 0 then 'NO' else 'YES' END as ISENABLED,

USERS.EMAILADDRESS as "USERS_EMAILADDRESS",

USERS.ISDELETED as "USERS_ISDELETED",

USERS.DESCRIPTION as "USERS_DESCRIPTION",

USERS.LASTLOGONTIME as "USERS_LASTLOGONTIME",

USERS.CREATED as "USERS_CREATED",

USERS.UPDATED as "USERS_UPDATED"

FROM

USERGROUPS

LEFT OUTER JOIN V_USERUSERGROUPS USERUSERGROUPS ON (USERUSERGROUPS.USERGROUP_ID=USERGROUPS.ID)

LEFT OUTER JOIN USERS ON (USERUSERGROUPS.USER_ID=USERS.OBJECTID) and USERGROUPS.CMSID = USERS.CMSID AND USERGROUPS.ISDELETED = 0 and USERS.ISDELETED = 0