Ektron – User Access List

Similar to Ektron SQL – List All Users

If you simply need a list of all of your CMS users whether they are admin or otherwise this script will help

SELECT [user_name] AS 'Username',
	([first_name] + ' ' + [last_name]) AS 'Name'
FROM [user_to_group_tbl] utgt
LEFT OUTER JOIN [usergroups] ug ON ug.usergroup_id = utgt.[usergroup_id]
LEFT OUTER JOIN [users] u ON u.user_id = utgt.user_id
WHERE usergroup_name IN ('Everyone')
ORDER BY username

If you want to show a break down of the groups that each user is in this script will help

SELECT --ug.[usergroup_id],
	[usergroup_name] AS 'User Group',
	--utgt.[user_id],
	[user_name] AS 'Username',
	([first_name] + ' ' + [last_name]) AS 'Name'
FROM [user_to_group_tbl] utgt
LEFT OUTER JOIN [usergroups] ug ON ug.usergroup_id = utgt.[usergroup_id]
LEFT OUTER JOIN [users] u ON u.user_id = utgt.user_id
WHERE usergroup_name IN (
		'Administrators',
		'Approvers',
		'Everyone'
		)
ORDER BY username,
	usergroup_name