I am doing a review of security that has been placed on a database for my company as controlled by an ex and non-IT employee. A new manager wants to review the security to this business critical database.

Security is currently controlled mostly through groups that assigns the writes to the objects (tables, queries, etc) as required. There are a small number of cases where security is assigned to individuals but these are most definitely the exception and not the rule.


I would like to present the information to the Manager and have this on record for my IT staff in a format that is easy to use. After playing around for a while with several formats I have settled on the possibility of doing the following:
  1. Building a report that shows the security in a much simpler format than the Documenter presents. Something like a matrix showing the groups/users down the page with the objects across the page with the security level (eg. data read, design read, etc) in the main section of the report.
  2. Have a module or query that can export the data in a two-dimensional format that can then be placed into an Excel spreadsheet to provide the content for a PivotTable. The spreadsheet could then be provided to the Manager and he can slice and dice the data how he wants to view it.
Personally the second option is my preferred option and if I could do this by giving him the ability to do the export whenever he wants then this would be ideal.



I have opened up the secured.mdw file on its own. I can see 11 system tables and four queries when I ensure that the Show Hidden or System Objects options are selected.

Has anybody done something like this or knows how it could be done?