User security report direct from SQL

We are doing a semi-annual security audit for Great Plains 2010. In the past I have provided the auditors pdf files generated from the User Security report, one file per user per company. This is a painful way to do this, especially as the user count grows.

What we dream of is a sql based report listing something along the same lines, but all in one spreadsheet:

UserID: BobH                    UserName: Bob Harrison

Company: Acme Main

Role ID:                                    Role Name

AR CLERK                              Account Receivables Clerk

Task ID                                           Task Name

------------------------------------------------------------------------------

ADMIN_SALES_005                      Reconcile Receivables Amount

ADMIN_SALES_008                      Post scheduled Customer Payments

...

Company: Acme Mail Order

Role ID                                      Role Name

AR CLERK                               Accounts Receivables Clerk

Task ID                                               Task Name

and so on until I get to the next user...

This is basically the format for the User Security report that prints, but I'd like to pull the data directly from the sql tables.

Of course, the fact that Dynamics is somewhat obtuse with its table names makes this a challenge.

Does anyone have either a sql script that is close to this, or be able to point me towards the right tables?

Thanks

Steve

 

 

Related
Recommended