To get the created datetime and created by for Security role assigned to user in AX 2012

Question

Is it possible to get CreatedDateTime and Createdby for the security role added to the user through sql query

This is query used to get the Security role

select distinct a.id as username , a.NAME Name, f.Text [Role] 
from userinfo a (nolock)  join securityuserrole b (nolock) on a.id=b.user_      
join SECURITYUSERROLECONDITION c (nolock) on b.recid = c.securityuserrole       
join  [Dynamics_STG_model].[dbo].[ModelSecurityRole] e (nolock) on e.rolehandle = b.securityrole        
join [Dynamics_STG_model].[dbo].[ModelElementLabel] f (nolock)  on e.LABELID = f.LabelId and e.LABELMODULE = f.Module and f.Language='en_us'

Please help me regarding this. Thanks in Advance!

Answer 1

I figured out the answer by myself:

Declare @StartDate datetime 
Declare @EndDate datetime 
Set @StartDate = '2015-07-07 01:31:38.000' 
Set @EndDate = '2015-08-21 01:14:14.000' 
Set @StartDate = convert(datetime, dateadd(hour, 7, @StartDate), 100) 
Set @EndDate = convert(datetime, dateadd(hour, 7, @EndDate), 100)

select distinct a.NAME [User Name]
               ,b.USERNAME [Role Modified By]
               ,convert(datetime, dateadd(hour, -7, b.CREATEDDATETIME), 100) [Modified DateTime]
               ,case b.LOGTYPE 
                   when 0 then 'Added'
                   when 1 then 'Removed' 
                end as Status
               ,d.Text Role 
from USERINFO a (nolock) 
join(
    select (case when logtype = 0 then dbo.CONPEEK(CAST(dbo.CONPEEK(data, 16) AS varbinary(8000)), 2)
                 when logtype = 1 then dbo.CONPEEK(CAST(dbo.CONPEEK(data, 10) AS varbinary(8000)), 2)           
            end) AS UserNam
           ,USERNAME
           ,CREATEDDATETIME
           ,logtype
           ,(case when logtype = 0 then dbo.CONPEEK(CAST(dbo.CONPEEK(data, 15) AS varbinary(8000)), 2)
                  when logtype = 1 then dbo.CONPEEK(CAST(dbo.CONPEEK(data, 9) AS varbinary(8000)), 2) 
             end) as SecurityRole    
    from SYSDATABASELOG (nolock) where TABLE_=65492 and data !='')  b  
on a.id = b.UserNam 
left outer join [DynamicsAX_model].[dbo].[modelsecurityrole] c (nolock) on b.SecurityRole =c.rolehandle 
join [modelelementlabel] d (nolock) on c.labelid = d.labelid 
                                   and c.LABELMODULE = d.module 
                                   and d.Language='en_us' 
where b.CREATEDDATETIME >= @StartDate and b.CREATEDDATETIME <=@EndDate order by [Modified DateTime] desc    

Conpeek and consize are two functions we need to create from the below link download ConPeek.sql and ConSize.sql