User Security - Direct vs. Inherited (Audit) * How to report where it comes from
We built only our different types of security (Data, WF, Access, Certify...) into Hierarchical structures. When using the SAR solution, it just lists all roles and does not say it is direct or indirect so for some users the list is very long. This has our Auditor very confused and asking why they need all these different roles when they really only have say 4. Anyway to run a report that would say this is direct (Parent Grps that Contain This User or Child Grps and Users) vs. indirect/Inherited (Parent Groups that Contain this Group).
Hi Clarke - I created something for a customer which only shows direct assignment - the query is below. You could take this and modify it to include a field which signifies these are Direct. And then add to the query the indirect assignments.
With dt AS
(
SELECT [Name], Description, IsEnabled, UniqueID
,CAST(REPLACE(CAST([XmlData] AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(UserDetail/Text1)[1]', 'varchar(max)') AS [Text1]
,CAST(REPLACE(CAST([XmlData] AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(UserDetail/Text2)[1]', 'varchar(max)') AS [Text2]
,CAST(REPLACE(CAST([XmlData] AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(UserDetail/Text3)[1]', 'varchar(max)') AS [Text3]
,CAST(REPLACE(CAST([XmlData] AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(UserDetail/Text4)[1]', 'varchar(max)') AS [Text4]
FROM [SecUser]
)
SELECT dt.Name AS [User Name], dt.Description, dt.IsEnabled, Text1, Text2, Text3, Text4, SecGroup.Name As [Group Name]
FROM dt
LEFT JOIN SecGroupChild ON SecGroupChild.ChildKey = dt.UniqueID
LEFT JOIN SecGroup ON SecGroup.UniqueID = SecGroupChild.GroupKey
Where dt.Name LIKE '%|!SelectedNameFilter_OSS!|%'
And dt.Description LIKE '%|!SelectedDescriptionFilter_OSS!|%'
And dt.IsEnabled LIKE '%|!SelectedIsEnabledFilter_OSS!|%'
And Text1 Like '%|!SelectedText1Filter_OSS!|%'
And Text2 Like '%|!SelectedText2Filter_OSS!|%'
And Text3 Like '%|!SelectedText3Filter_OSS!|%'
And Text4 Like '%|!SelectedText4Filter_OSS!|%'
