Script to know AD permission assigned on Archives/Folders
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:-
- 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.
- If AD object (user/group) is deleted then result for AD_Object column will come as ‘NULL’.
- 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.