SQL Query for Security Role Membership

[Migration User]

Does anyone out there have a SQL query that will return each security role and the user(s) names who are members of that security role?  If so, would you mind sharing it with me?  i am having some trouble locating the tables necessary to build such a query.  Thanks.  Version 6

[Migration User]

There is no table in NS 6 where user name is directly stored in place it’s stored the sid in SecurityTrustee table. Also there are other security tables where roles, permission and privilege get stored releated to entity. But I am not sure you will get the required information by just query.
 

[Migration User]

I use this to find out about the premissions in NS6. Maybe not what you are looking for, but it gives you a start.

select sr.name as 'Security Role',
sg.nameref as 'Display Group',
sp.name as 'Privilege' 
from securityrole sr
join securityprivilegetrustee spt 
on spt.trusteeguid = sr.trusteeguid
Join SecurityPrivilege sp 
on sp.guid = spt.privilegeguid
join SecurityPrivilegeDisplayGroup sg 
on sg.guid = sp.displaygroupguid
where sr.name like '%'
order by sr.name, sg.nameref
 

[Migration User]

Is there anywhere in the database where the SID is associated directly with a user?  It seems like there should be  way to link this information back to get the user names associated with each role.

[Migration User]

Thanks CliffL  This helps...now if only I could find out how to tie the user name to this.  I thought that maybe I could use the SecurityTrustee table which contains the user SID, but I can't find a table now that ties the SID back to a user name.  Just  little frustrating.

[Migration User]

Try this.
https://kb.altiris.com/article.asp?article=43874&p=1