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')
);

FWIW, I've just encountered this bug in a production system & another fix for this issue is to generate a new license from the Alfresco Support portal and then apply that. It seems that a new license resets the checksums.

@davidcognite I actually haven't tried that since everytime this happened, it was a "criticality negative 1" issue without being able to wait on either Support or someone from the customer who is able to access Support in the first place (since I am no longer a partner since going into business for myself, I don't have access to the portal myself at any of my customers, usually in part due to concerns over German regulation regarding so-called ANÜ). I'll keep that in mind for one of the next times to see if we can't try it that way.

I had to adapt the script as apparently there are minor difference between ACS versions, specifically 5.x and 6.x+. A reset via the original script on a 6.2.2 system failed partially, as the licensed user count was not correctly reset.