Solved

Microsoft Access user-level security, get list of user permissions

Posted on 2014-09-09
6
913 Views
Last Modified: 2014-09-24
I want to upgrade an Access 2003 Database to Access 2010. The database has many tables and forms where permissions are limited to certain users. I want to revise that and will have to build in some user permission control in the upgraded database.
I want a list of all files and forms in the database with all their permissions.
I can get a list of user and group accounts which gives me a list of all users and their associated groups and also the other way round, all the groups with all their users. I get that from User and Group Accounts in Users and Permissions in Database Tools in the 2010 ribbon.
I think I have seen a list like what I want previously.
Does anybody know how I can get such a list please.
0
Comment
Question by:Fritz Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40312160
I can't verify this since I am using A2013 but you should be able to find the collection that contains the data you are seeking and by using VBA, loop through and extract what you need.  There might even be a useful MSys table which would make it easier.  Unhide the systems tables and start there.
0
 

Author Comment

by:Fritz Paul
ID: 40317365
Hi Pat,

The security file associated with this database is Secured.mdw. From there I can get lists of user and group names, but not how they relate to the tables and forms.

However one of the system files in the main database itself seems to have the information, but it is in "Long binary data". See the picture below. Do you know how to see that in readable format?

Through Database Tools > Database documenter I could get a report consisting of many pages that among other things contains the information if I export in txt or rtf format. I believe I will be able to open those up in Excel and eventually filter the required information out tediously. (The info can be exported in Excel, but then the names of tables and forms are not included.

Image of System file with definitions
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40317978
I know the logins are in the .mdw but I think the details regarding objects are in the database itself.  I don't have any old applications with security and I don't have anything older than A2010 to work with so I can't really investigate this for you but the answer lies with the collections.  Having a MSys table would have been too easy.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 250 total points
ID: 40331559
Our commercial Total Access Analyzer addin product performs detailed documentation and cross-referencing of your database objects and detects 300+ types of errors, suggestions, and performance tips.

Among its documentation are several workgroup security reports that include lists of users, groups, groups and users, and user/group permissions at the object type and individual object level. Examples of the reports it generates are shown here: Microsoft Access Workgroup Security Permissions for Users and Groups

This pretty helpful information if you're trying to understand the existing security and managing it over time. Leaving a hole in the security scheme could be devastating and Microsoft doesn't make it easy to have an overview of the settings.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40335442
If you want a roll-your-own sort of thing, you'll have to loop through the Containers and Documents collections, and determine the permission levels for each User associated with the object. This website has some information on the process:

http://www.casadebender.com/reference/other/bldapps/chapters/ba14_2.htm
0
 

Author Closing Comment

by:Fritz Paul
ID: 40341165
Thanks I appreciate.
Regards,
Fritz
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question