In the last post, I posted a SQL view which returned a list of users who had logged in before the current date. This post contains a SQL stored procedure which will delete any prior day login; this could be scheduled to run using SQL Server Agent:
CREATE PROCEDURE usp_AZRCRV_RemovePriorDayLogins AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK). */ DELETE FROM ACTIVITY WHERE ['User Activity'].LOGINDAT <= DATEADD(DAY, -1, GETDATE()) GO GRANT EXECUTE ONusp_AZRCRV_RemovePriorDayLogins TO DYNGRP GO
Before using this script on a live system, I’d recommend testing it on a standalone test system first.