Solved

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

Posted on 2014-09-09
6
840 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
6 Comments
 
LVL 36

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 36

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 84

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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