Finding out which SalesLogix users are in which roles in the Web Client

Here are some SQL scripts to allow you to find out which users are not in any roles in the system.  These users will not have access to certain actions in the SalesLogix web client like being able to save and or insert records.  Note this query excludes web viewers, retired users, and template users (USERSECURITY.TYPE of V, R, and P respectively).

select
a.username,
b.type,
d.rolename
from
userinfo a inner join
usersecurity b on a.userid=b.userid left join
userrole c on a.userid=c.userid left join
role d on c.roleid=d.roleid
where
d.roleid is null and b.type not in (‘V’,’P’,’R’)

Changing the Query slightly will show which roles the users are in:

select
a.username,
b.type,
d.rolename
from
userinfo a inner join
usersecurity b on a.userid=b.userid left join
userrole c on a.userid=c.userid left join
role d on c.roleid=d.roleid
where
d.roleid is not null and b.type not in (‘V’,’P’,’R’)