User deletion via queries

Document ID : KB000030086
Last Modified Date : 14/02/2018
Show Technical Document Details

User Deletion via Queries

In large environments, there is often a need to remove a certain set of users from the Domain Manager. This is because, whenever user validation happens, it results in very poor performance of the DSM explorer and DSM reporter. 

If the performance within the DSM Explorer and the DSM reporter is poor, we can improve the performance by deleting the users.

Following is a short script to do a batch deletion of users using queries.

Caution:

The user_uuids table is used to perform the deletions. Hence, it is very important to first check the uuids obtained to ensure that they fall within the set of users to be deleted. In addition, it is must to change the last_update_user < 1417426929 condition to delete the old users. It is advised to delete the users, which are not updated for the last three months. You can use http://www.epochconverter.com/ to convert human readable timestamp to system timestamp.

 

DECLARE @uuid varchar(50)

DECLARE @MyCursor CURSOR

 

SET @MyCursor = CURSOR FAST_FORWARD

FOR

 

SELECT user_uuid from ca_discovered_user WHERE last_update_date < 1417426929 and user_type=0

 

OPEN @MyCursor

FETCH NEXT FROM @MyCursor

INTO @uuid

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

DELETE FROM ca_group_member WHERE member_uuid in (select link_dis_hw_user_uuid from ca_link_dis_hw_user where user_uuid=@uuid)

DELETE FROM ca_agent_component WHERE object_uuid in (select link_dis_hw_user_uuid from ca_link_dis_hw_user where user_uuid=@uuid)

DELETE FROM ca_agent WHERE object_uuid in (select link_dis_hw_user_uuid from ca_link_dis_hw_user where user_uuid=@uuid)

DELETE FROM ca_link_dis_hw_user WHERE user_uuid=@uuid

DELETE FROM ca_agent_component WHERE object_uuid=@uuid

DELETE FROM ca_agent WHERE object_uuid=@uuid

DELETE FROM ca_discovered_user WHERE user_uuid=@uuid

 

FETCH NEXT FROM @MyCursor

INTO @uuid

END

CLOSE @MyCursor

DEALLOCATE @MyCursor

 

Steps to run the above query:

  1. Login to SQL Server Management Studio with the ca_itrm user account.
  2. Open a new query window and execute the above cursor code against the MDB.

Note: It is recommended to take a full backup of the MDB before performing the operation above.

File Attachments:
TEC1927875.zip