Video Screencast Help

SQL Query for Security Role Membership

Created: 16 Jun 2010 | 5 comments

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

Comments 5 CommentsJump to latest comment

Ashutosh Thaokar's picture

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.
 

gtingen's picture

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.

CliffL's picture

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
 

gtingen's picture

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.