locked
Get the list of all the records owned by a User in CRM 2011 RRS feed

  • Question

  • How can I get the list of all the Records (of all the entities) the Users is owning in the CRM 2011? 

     The scenario when a user moves out of an organization either the User has to be disabled or deleted. If the user has to be disabled then all the records assigned to User has to be assigned to some other user/team.

    I have done some analysis, the records will remain as they are unless any action is taken on them, except the Activities (Tasks/Appointments) assigned to the user which have to be completed before the User has to be disabled, (this is OOOB validation). We cannot disable the users until the activities are completed.

    There is a option with Reassign all records on User Form which will assign all the records irrespective of the state of the records, like opportunities which are won, tasks which are completed, any active or inactive records.

    Does anyone has any better solution then manually finding records for each of the entities manually. 


    Wednesday, January 13, 2016 1:49 PM

All replies

  • Hello,

    I believe there is no OOB functionality for this but as for me this can be developed.


    Dynamics CRM MVP
    My blog

    Wednesday, January 13, 2016 2:24 PM
    Moderator
  • Yes I understand that there is no OOOB functionality for this. 

    I am planning to develop a tool for this.. But I thought if any one has done it past could help me in producing the query (SQL Query)..

    Anyway thanks for your response.

    Wednesday, January 13, 2016 2:40 PM
  • Here is query that can help you:

    Declare @SQL VarChar(Max), @EntityName VarChar(Max), @ReportViewName VarChar(Max), @UserId VarChar(Max), @IsActivity bit
    
    Set @SQL = ''
    Select @UserId = dbo.fn_FindUserGuid()
    
    
    DECLARE EntityCursor CURSOR FOR 
    Select
    	distinct LogicalName, IsActivity, ReportViewName 
    From Entity
    Where OwnershipTypeMask = 1 and ReportViewName Is Not Null
    
    Open EntityCursor
    
    Fetch Next FRom EntityCursor
    Into @EntityName, @IsActivity, @ReportViewName
    
    While @@FETCH_STATUS = 0
    Begin
    	if (@EntityName = 'activitypointer' OR @IsActivity = 1)
    	begin
    			Set @SQL = 'Select activityid as RecordId, ''' + @EntityName + ''' as EntityName From ' + @ReportViewName + ' where ownerid=''' + @UserId + ''''
    	end
    	else
    	begin
    		Set @SQL = 'Select ' + @EntityName + 'id as RecordId, ''' + @EntityName + ''' as EntityName From Filtered' + @EntityName + ' where ownerid=''' + @UserId + ''''
    	end
    
    	Set @SQL = 'IF Exists(' + @SQL + ') Begin ' + @SQL + ' End'
    	Exec(@SQL)
    
    	Fetch Next FRom EntityCursor
    	Into @EntityName, @IsActivity, @ReportViewName
    End
    
    Close EntityCursor
    Deallocate EntityCursor


    Dynamics CRM MVP
    My blog

    Wednesday, January 13, 2016 4:29 PM
    Moderator