Reset Alfresco Authorised Users tracking (only meant for fixing errors, i.e. MNT-16663, or false-positive 'manual changes' detection - not to circumvent subscription terms)

reset_alf_auth_script.sql

-- clear all user license states
delete from alf_auth_status;

-- remove old checksum and keystore
-- ns_id = 1 should always be the system model, store_id = 2 always the system store
delete from alf_node_properties where qname_id in (
  select id from alf_qname where ns_id = 1 and local_name in ('keyStore', 'authorizationChecksum')
) and node_id in (
  select id from alf_node where store_id = 2
);

-- remove keystore alias info
delete from alf_prop_unique_ctx where id in (
	select puc.id
	from alf_prop_unique_ctx puc
		left join alf_prop_value pv1 on pv1.id = value1_prop_id and pv1.persisted_type = 3
		left join alf_prop_value pv2 on pv2.id = value2_prop_id and pv2.persisted_type = 3
		left join alf_prop_string_value psv1 on pv1.long_value = psv1.id
		left join alf_prop_string_value psv2 on pv2.long_value = psv2.id
	where psv1.string_value = 'keyCheck' and psv2.string_value = 'authorization'
);

-- reset user usage count
delete from alf_prop_unique_ctx where id in (
	select puc.id
	from alf_prop_unique_ctx puc
		left join alf_prop_value pv1 on pv1.id = value1_prop_id and pv1.persisted_type = 3
		left join alf_prop_value pv2 on pv2.id = value2_prop_id and pv2.persisted_type = 3
		left join alf_prop_value pv3 on pv3.id = value3_prop_id and pv3.persisted_type = 3
		left join alf_prop_string_value psv1 on pv1.long_value = psv1.id
		left join alf_prop_string_value psv2 on pv2.long_value = psv2.id
		left join alf_prop_string_value psv3 on pv3.long_value = psv3.id
	where psv1.string_value = '.repoUsages' and psv2.string_value = 'current' and psv3.string_value in ('users', 'authorizedUsers', 'lastUpdateUsers')
);