Grant read permission for MSysObjects

Question

I need to get the metadata of some hundred MS Access DBs, so I need to automate the process of metadata gathering. I want to query stuff described here, but before I can query the DBs I need read access to MSysObjects tables.

I Keep getting following error:

Record(s) cannot be read; no read permission on 'MsysObjects'.

so I dug deeper:

I've read the article about similar issues in the C# section (see here and also this one).

I put together a simple script which works well for creating tables and so on, but I am having problems to setup the read access.

$ScrUsr = $(whoami)
Write-Host $ScrUsr

$cmd = "GRANT SELECT ON MSysObjects TO [$ScrUsr]"
Write-Host $cmd

Function Invoke-ADOCommand($Db)
{
  $connection = New-Object -ComObject ADODB.Connection
  $connection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$db;" )
  $command = New-Object -ComObject ADODB.Command
  $command.ActiveConnection = $connection
  $command.CommandText = $cmd
  $rs = $command.Execute()
  $connection.Close()
} 

$Db = "C:\Temp\test1.accdb"

Invoke-ADOCommand -db $Db

Executing the Script throws me following Error reason:

Cannot open the Microsoft Office Access database engine workgroup information file.

How can I allow read permission on MSysObjects?

I don't think its the problem but your parameters for your function have an anomoly in them `Invoke-ADOCommand($Db, $Command)` you dont use `$command` when you _call_ the function so just remove it. Is that database password protected? — Matt, Sep 29 '15 at 12:55
Thanks for the hint, already fixed, script works because the $command variable will be set in the function. Nop DB is not Password protected. I already tried also Grant to Public but this didn't work either. — rammy, Sep 29 '15 at 13:57
Have you tried this? ... `GRANT SELECT ON MSysObjects TO Admin;` — HansUp, Sep 29 '15 at 14:05
Jepp, was my Initial Statement firing against the MdB, same errorMsg — rammy, Sep 29 '15 at 14:22

HansUp · Accepted Answer · 2015-09-29T17:16:10.230

There are two issues you must address:

Your code will use the Access security account "Admin" when interacting with the Access ACCDB database. Other Access security accounts are only available if you have implemented Access user-level security (ULS), but that feature is only available for MDB format databases. Therefore, with ACCDB, you can't GRANT anything to any account other than "Admin".
You must include the location of the "System database" in your connection string.

If you don't know where to find the System database, open Access and check the output from this statement in the Immediate window. (Ctrl+g will open the Immediate window)

Debug.Print Application.DBEngine.SystemDb

My System database is C:\Users\hans\AppData\Roaming\Microsoft\Access\System1.mdw Substitute yours in the code example below.

I'm not really proficient with Powershell, but this example ran without error for me. (Be sure to change both $Db and $SystemDb.)

$ScrUsr = $(whoami)
Write-Host $ScrUsr

$cmd = "GRANT SELECT ON MSysObjects TO Admin;"
Write-Host $cmd

Function Invoke-ADOCommand($Db, $SystemDb)
{
  $connection = New-Object -ComObject ADODB.Connection
  $ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$db;Jet OLEDB:System database=$SystemDb;"
  Write-Host $ConnectionString
  $connection.Open($ConnectionString)
  $discard = $connection.Execute($cmd)
  $connection.Close()
} 

$Db = "C:\Users\hans\AccessApps\Sample.accdb"
$SystemDb = "C:\Users\hans\AppData\Roaming\Microsoft\Access\System1.mdw"

Invoke-ADOCommand -db $Db -SystemDb $SystemDb

nop, good one would be if one is using try and catch error handling to run it controlled, but for a starting Point & testing purpose it is usable. thanks again. — rammy, Sep 30 '15 at 06:59

Grant read permission for MSysObjects

1 Answers1