Script to know AD permission assigned on Archives/Folders

[Pradeep-Papnai]

Most of time we need a script/SQL query to know permission assigned or delegated to Archives or archive folders. This information can also be found from the Vault Admin Console but it’s difficult when we have large no of archives, additionally vault admin console does not allow to make a report of permission details. Here are the SQL queries which can make our job easy for reporting/troubleshooting purpose.

Get permission assigned on archives.

Use EnterpriseVaultDirectory

Go

If OBJECT_ID (N'dbo.sidconvert', N'FN') IS NOT NULL

    DROP FUNCTION sidconvert;

GO

CREATE FUNCTION dbo.SidConvert (@MYSID AS VARCHAR(max))

RETURNS varbinary(256)

As

BEGIN

       If LEN (@MYSID)>12

                     BEGIN

                     DECLARE @A AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT

                     SET @MYSID = REVERSE(@MYSID)

                     SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

                     SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)

                     SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

                     SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)

                     SET @B = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

                     SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)

                     SET @A = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

                     RETURN 0x010500000000000515000000

                     + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))

                     + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))

                     + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))

                     + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))

                     END

              BEGIN 

              RETURN null

              END

END

GO

SELECT ArchiveName, rt.VaultEntryId ArchiveID,

      CASE rt.Type WHEN 5 THEN 'Shared' WHEN 9 THEN 'Exch_Mailbox' WHEN 17 THEN 'Exch_Journal' WHEN 33 THEN 'Public_Folder'

      WHEN 65 THEN 'SharePointServer' WHEN 129 THEN 'FSA' WHEN 257 THEN 'Sharepoint' WHEN 513 THEN 'Lotus_Journal'

      WHEN 1025 THEN 'Lotus_Mbx' ELSE 'Undefined' END AS ArchiveType,

      CASE ac.ACEType WHEN 0 THEN 'Inherited_Only' WHEN 1 THEN 'Manual_Only' WHEN 2 THEN 'Mixture' ELSE 'No_Permission' END

      AS 'PermissionType',

      CASE ISNULL(tt.sid,'No') WHEN 'No' THEN 'No Permission' ELSE tt.sid  END SID,

      CASE ISNULL (tt.SID, 'No') WHEN 'NO' THEN 'No Permission'  WHEN 'S-1-3-0' THEN 'CREATORE_OWNER' When 'S-1-1-0' then 'EVERYONE'

      WHEN 'S-1-5-11' THEN 'AUTHENTICATED_USER' WHEN 'S-1-5-32-544' Then 'BUILT-IN ADMINISTRATORS' WHEN 'S-1-5-32-545' Then 'USERS'

      WHEN 'S-1-5-18' THEN 'LOCAL_SYSTEM' WHEN 'S-1-5-32-547' THEN 'POWER USERS'

      Else SUSER_Sname(dbo.sidconvert(tt.SID))  END 'AD_Object'

FROM EnterpriseVaultDirectory.dbo.Archive

            INNER JOIN EnterpriseVaultDirectory.dbo.Root rt ON Archive.RootIdentity = rt.RootIdentity

            LEFT JOIN EnterpriseVaultDirectory.dbo.ACE ac ON ac.RootIdentity = Archive.RootIdentity

            LEFT JOIN EnterpriseVaultDirectory.dbo.Trustee tt ON tt.TrusteeIdentity = ac.TrusteeIdentity

ORDER BY ArchiveName

Go

DROP FUNCTION SidConvert;

Get permission assigned on archive folders on given archive:-

This query need to run against vault store database and we need to supply ArchiveID of given archive.

USE VaultstoreDatabase

GO

If OBJECT_ID (N'dbo.sidconvert', N'FN') IS NOT NULL

  DROP FUNCTION sidconvert;

GO

CREATE FUNCTION dbo.sidconvert (@MYSID AS VARCHAR(max))

RETURNS varbinary(256)

 As

Begin

  If LEN (@MYSID)>12

  BEGIN

  DECLARE @A AS BIGINT ,@B AS BIGINT ,@C AS BIGINT ,@D AS BIGINT

  SET @MYSID = REVERSE(@MYSID)

  SET @D = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

  SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)

  SET @C = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

  SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)

  SET @B = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

  SET @MYSID = SUBSTRING(@MYSID,CHARINDEX('-',@MYSID)+1,255)

  SET @A = REVERSE(SUBSTRING(@MYSID,1,CHARINDEX('-',@MYSID)-1))

  RETURN 0x010500000000000515000000

  + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@A)))

  + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@B)))

  + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@C)))

  + CONVERT(VARBINARY,REVERSE(CONVERT(BINARY(4),@D)))

  END

  BEGIN

  RETURN NULL

  END

END

GO

 --Modify @ArchiveName parameter to specify the archive to investigate

DECLARE @ArchiveID Nvarchar(75)

SET @ArchiveID = '1181B6B5FA3F3B94DA92E8AF4A2D509671110000EVS1'

SELECT

  CASE AF.FolderName WHEN 'Top of Information Store' THEN 'Mailbox_Root' else af.FolderName end 'Folder_Name' ,

  VT.VaultID 'Folder_ID', vt.ArchivedItems 'No_Of_items', vt.ArchivedItemsSize 'Total_Size(kb',

  tt.SID , CASE tt.SID WHEN 'S-1-3-0' THEN 'CREATORE_OWNER' When 'S-1-1-0' THEN 'EVERYONE' WHEN 'S-1-5-11' THEN 'AUTHENTICATED_USER'

  WHEN 'S-1-5-32-544' Then 'BUILT-IN ADMINISTRATORS' WHEN 'S-1-5-32-545' Then 'USERS' WHEN 'S-1-5-18' THEN 'LOCAL_SYSTEM'

  WHEN 'S-1-5-32-547' Then 'POWER USERS' Else SUSER_Sname(dbo.sidconvert(tt.SID)) END AD_OBJECT

FROM Vault vt

  INNER JOIN EnterpriseVaultDirectory.dbo.ArchiveFolderView af on af.VaultEntryId = vt.VaultID

  INNER JOIN EnterpriseVaultDirectory.dbo.Root rt on rt.RootIdentity = af.RootIdentity

  LEFT JOIN EnterpriseVaultDirectory.dbo.ACE ac on ac.RootIdentity = rt.RootIdentity

  LEFT JOIN EnterpriseVaultDirectory.dbo.Trustee tt on tt.TrusteeIdentity = ac.TrusteeIdentity

WHERE af.ArchiveVEID =@ArchiveID

DROP FUNCTION sidconvert;

ADDITIONAL NOTES:-

1. Above query does not give granular details of permission such as ‘Allow’ or ‘Deny’  & level of access ‘Read/Write/Delete’  as that save in encrypted form within database, to know granular detail then permission browser should be use.  
2. If AD object (user/group) is deleted then result for AD_Object column will come as ‘NULL’.
3. Most of common AD object such as Guest, Everyone..etc have same SID for every Active directory environment, we have used few of the commonly used objects taking reference from KB http://support.microsoft.com/kb/243330 and placed in SQL query, If query returns any other common AD object then SQL query needs to modify.