Querying users and security groups

Unbelievably, it is possible to part with your hard-earned cash for commercial tools that, amongst other things, present a summary of user/group membership in an easy-to-interpret way. People can get away with it because these tools are much easier to use and interpret in this respect than Access's integrated view of the same information, which constrains you to looking at either all the members of one group or all the group memberships of one user. Even I, the most ardent of Access-philes, have to admit that sucks.

So what's the solution? Part with your hard-earned cash? Never! Instead, let's delve deeper into your system.mdw file. For the uninitiated, this is the workgroup information file that controls the security information for the database in question. There is a default workgroup information file that is used for all databases unless you specify otherwise via the /wrkgrp command line parameter. But I digress...

Take a look in the system.mdw file - I find my default version at C:\Program Files\Common Files\System\system.mdw (I'm using Access 2000, by the way). Equally, you can open up any copy of a system.mdw file that you might be using for other databases. Either way, you'll find a couple of tables in there, MSysAccounts and MSysGroups. If you can't see these: go to Tools, Options and check System Objects on the View tab. You'll also find some queries, notably MSysGroupMembers and MSysUserMemberships which are on the way to what we want but still constrain you to the one user/group at a time view mentioned earlier.

So where were we? Well, as the name suggests, the MSysAccounts and MSysGroups tables store info on the users and security groups in your workgroup information file, so we can write a neat crosstab query to provide a grid of which users belong to which groups, like so:

TRANSFORM Count(MSysGroups.GroupSID) AS CountOfGroupSID
SELECT MSysAccounts.Name
FROM (MSysAccounts INNER JOIN MSysGroups ON MSysAccounts.SID=MSysGroups.UserSID)
INNER JOIN MSysAccounts AS MSysAccounts_1 ON MSysGroups.GroupSID=MSysAccounts_1.SID
WHERE (((MSysAccounts.FGroup)=0) AND ((MSysAccounts_1.FGroup)<>0))
GROUP BY MSysAccounts.Name
PIVOT MSysAccounts_1.Name;

Go on, give it a try! It's pretty tidy, isn't it? One thing to note - you don't have to do this in the system.mdw - if you'd rather integrate this crosstab in your database application, simply create links to the two tables in the relevant system.mdw file (File, Get External Data, Link Tables...) and then you can create the query above in your database file.