User Security - Direct vs. Inherited (Audit) * How to report where it comes from
- 3 years ago
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!|%'