make a report that will show users and windows uses by them

(0) Share

Report

Posted on by Community Member

Hi,

I will like to know in which table I could found all the windows names in GP 2010 r2 and the table that link the Task and windows, with that I could make a report (Crystal Report) that show for each user which windows he have access to.

thanks!

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Mike Smith 6,840 on at

Like (0)

Report
Copy link

Link copied!

I haven't tested this but it looks like you can use:

SY10500 (User and Security Role)

SY10600 (Security Task)

SY10700 (Looks like a table between Security Task and Windows/Reports/Etc...)

SY09400 (Product, Type (Window), Display Name, Series)

Heres the query I used to pull this together:

SELECT

USERS.USERID,

USERS.SECURITYROLEID,

SECURITYTASKS.SECURITYTASKID,

WINDOWACCESS.PRODNAME,

WINDOWACCESS.TYPESTR,

WINDOWACCESS.DSPLNAME,

WINDOWACCESS.Series_Name

FROM

SY10500 AS USERS

LEFT OUTER JOIN SY10600 AS SECURITYTASKS ON USERS.SECURITYROLEID = SECURITYTASKS.SECURITYROLEID

LEFT OUTER JOIN SY10700 AS SECURITYTASKDEF ON SECURITYTASKS.SECURITYTASKID = SECURITYTASKDEF.SECURITYTASKID

LEFT OUTER JOIN SY09400 AS WINDOWACCESS ON SECURITYTASKDEF.SECURITYID = WINDOWACCESS.SECURITYID

WHERE WINDOWACCESS.TYPESTR = 'Windows'

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Mike,

But didn't work, the query result is empty... maybe because my sy09400 table is empty?... I did create some users and link some role to them?

Alain

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report
Copy link

Link copied!

Hi Alain,

This may be what you're looking for: victoriayudin.com/.../sql-view-with-security-resource-details-for-dynamics-gp-10. While it says it's for GP 10, it will also work for GP 2010 (I will add a note in there to reflect that). To populate the SY09400 table you will need to follow the steps above the script. :-)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Victoria,

Your the best ... :-)

Alain

Was this reply helpful? Yes No
Meimei 5 on at

Like (0)

Report
Copy link

Link copied!

Hi,

I followed the steps to do clear data for the table, but the sy09400 table is still blank, anybody have any idea why?

Thanks!

Was this reply helpful? Yes No