SQL query that reports a list of individual users that have access to any given doctype

[Michael_Newton]

Title says it all. Looking for a SQL query that reports a list of individual users that have access to a doctype, the query would need to sift through the groups that have access to said doc type in order to come up with a definitive list. It would be awesome if the query showed what group the user belonged to as well which was granting them access to said doc type.

Thank you for any assistance you can provide!

[Larissa_Armand · Accepted solution]

Hi Michael,

This is what i use on a reporting dashboard: 

select rtrim(G.USERGROUPNAME) as GroupName, trim(U.USERNAME), trim(U.REALNAME), rtrim(D.ITEMTYPENAME) as DocTypeName, U.LASTLOGON, U.DISABLELOGIN
from HSI.USERGROUPCONFIG X
left outer join HSI.USERGROUP G ON X.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.DOCTYPE D ON D.ITEMTYPENUM = X.ITEMTYPENUM
left outer join HSI.USERXUSERGROUP UXG ON UXG.USERGROUPNUM = G.USERGROUPNUM
left outer join HSI.USERACCOUNT U ON UXG.USERNUM = U.USERNUM
where trim(D.ITEMTYPENAME) = @{pConfigItemName}
and trim(G.USERGROUPNAME) != 'MANAGER'

My data provider has a select list for the parameter so you can choose which document type you want from a list. https://support.hyland.com/r/OnBase/Reporting-Dashboards/English/Foundation-22.1/Reporting-Dashboard... 

[Brent_Fuller]

Michael,

I have a SQL query I built that outputs what you want, and more. It runs on either a doc type number or doc type group, outputs the groups that have access to it, what privileges those groups grant to the doc type, each user who has access, and what level of privileges (and override privileges) each user has on the doc type. The SQL query is a bit lengthy to post here. If you get your email address to me I'll shoot over the query. 

Brent Fuller

[Michael_Newton]

Brent, that would be amazing. Can you send said query to mikenewton902@gmail.com

? (I'm sure it'll just get blocked if we send it to my work email address)

I'd love to take a look!

[Brent_Fuller]

I've sent it over. Let me know if you don't receive it in case it got caught on our outbound security.