Determine Permissions on User Archive - SQL Query

[AKL]

Hello

I am running an Enterprise vault 9.0.1.1073 environment along with Exchange 2010 and SQL 2008 R2.

I have a powershell script which generates a csv file that contains details of archives which have Mailbox which is not in nomal state and/or AD account which is not in normal state.

This csv file is then emailed and is used for deleting/removing any orphaned archive.

However, there's one more parameter which I need to complete and that is archives which have read/write access on them for some other user account than default (like User B has permissions on User A archives apart of him/her)  - So we do not delete archives which are still accessed (we don't have auditing configured)

Is there a way to pull this information from SQL? I know there're some extra tools like PermissionExplorer.exe but to complete the powershell script, I'll need SQL statement to pull out this information....

I'll surely share complete script once it is complete along with this information.

Thanks in advance

[JesusWept3 · Accepted solution]

Unfortunately its a fairly complex procedure, its not in clear text, its in a bin format, so you'd need to decipher different things such as the whole read/write/delete/control folder (allow and deny etc)
 

[AKL]

Thanks Jesus - I can try to work that out.... If you can point me in a direction where these values are stored?

[JesusWept3]

In EnterpriseVaultDirectory.dbo.Root you have AutoSecurityDesc and ManualSecurityDesc
It will typically look like this for the output

0x0100048000000000000000000000000014000000020050000200000000022400BF040200010500000000000515000000E526610A9E353A00AE091B16714E000000002400BF040000010500000000000515000000A9F1A27AB0E5DE4CEC8DCA7462050000

Good luck!