Forum Discussion

Clarke_Hair's avatar
Clarke_Hair
Contributor
3 years ago

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 indire...
Security
  • EricOsmanski's avatar
    EricOsmanski
    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!|%'

     

     

EricOsmanski's avatar
EricOsmanski
Valued Contributor
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!|%'