Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-09
6
Medium Priority
?
986 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 39

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 39

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 500 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

670 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