User Group Security ID

[Curtis_David]

I've been asked to create a report on users in groups.  They want all the groups in OnBase collected and a lookup of the corresponding groups in Active Directory reported.  SInce groups are aligned between AD and OnBase using the SID (sechrityID) where can I find the SID of the groups in the OnBase DB?  Users have their SID's stored in the DB. Hopefully, I've simply misinterpreted something and missing the obvious.

Thanks,

Curt

[AdamShaneHyland]

Hi Curt,

Thanks for the post.

For any Active Directory usergroup mapped to OnBase using the Active Directory (or Active Directory - Enhanced in OnBase 14 and higher) security method, the associated SID is stored in the hsi.secprincipals.  The following query will list the domain security principal, principal's sid and respective mapped OnBase user group.

SELECT 	sp.principalname AS 'AD User Group Name',	sp.principalsid AS 'AS User Group SID',	ug.usergroupnum AS 'OnBase User Group Num',	ug.usergroupname AS 'OnBase User Group Name'FROM hsi.secprincipal spJOIN hsi.usergroup ug ON sp.usergroupnum = ug.usergroupnumORDER BY sp.principalname ASC

The user SIDs are stored in the hsi.useraccounts table.

Take care.

[Curtis_David]

Adam,

Thanks a lot!  Exactly what I need.

[AdamShaneHyland]

My pleasure.