As any system, also your Sitecore installation will historically grow. And for sure, none of the users will tell you when he or she stops working on Sitecore or leaves the company. Also noone of the local administrators (in case you have some) will tell. So you do not only have a cleanup issue but might also have a security issue.
The following Script will tell you what users have not been active since a certain date. This limiDate you can set at the top. To execute you only need "Read" rights on your databases. So admins will probably give you read rights also on production systems.
Read before run:
Updates:
16.03.2017 - I recently found out that LastActivityDate in aspnet_Users does not reflect really the last activity of the user itselft. This date is also updated when User is called from user manager as user is validated in that moment. Better used is here LastLoginDate from aspnet_Membership.
/****** Script for SelectTopNRows command from SSMS ******/
BEGIN
PRINT '***********************************';
PRINT 'Inactive Users';
PRINT '***********************************';
/*ToDO: Define limit Date*/
DECLARE @limitDate datetime = CONVERT(datetime, '01.01.2017', 104);
DECLARE @UserId nvarchar(128);
DECLARE @UserName nvarchar(128);
DECLARE @LoweredUserName nvarchar(128);
DECLARE @LastActivityDate datetime;
DECLARE @LastLoginDate datetime;
DECLARE @RoleId nvarchar(128);
DECLARE @RoleName nvarchar(128);
DECLARE @RoleDescription nvarchar(128);
DECLARE @UserCount int = 0;
DECLARE INACTIVE_USERS cursor FOR
SELECT u.UserId
,u.UserName
,u.LoweredUserName
,u.LastActivityDate
,m.LastLoginDate
FROM aspnet_Users u, aspnet_Membership m
WHERE u.UserId = m.UserId
AND m.LastLoginDate< @limitDate
ORDER BY LastActivityDate ASC;
OPEN INACTIVE_USERS;
FETCH NEXT FROM INACTIVE_USERS into @UserId, @UserName, @LoweredUserName, @LastActivityDate, @LastLoginDate;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @UserCount = @UserCount + 1;
PRINT 'User' + ': ' + @UserName + ' (' + CAST(@LastLoginDate AS VARCHAR) + ')';
DECLARE ROLES_OF_USER CURSOR FOR
SELECT r.RoleId
,r.RoleName
, r.Description
FROM aspnet_Roles r , aspnet_UsersInRoles ur
WHERE r.RoleId = ur.RoleId
AND ur.UserId = @UserId;
OPEN ROLES_OF_USER;
FETCH NEXT FROM ROLES_OF_USER into @RoleId, @RoleName, @RoleDescription;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' Role' + ': ' + @RoleName;
FETCH NEXT FROM ROLES_OF_USER into @RoleId, @RoleName, @RoleDescription;
END;
CLOSE ROLES_OF_USER;
DEALLOCATE ROLES_OF_USER;
FETCH NEXT FROM INACTIVE_USERS into @UserId, @UserName, @LoweredUserName, @LastActivityDate,@LastLoginDate;
END;
CLOSE INACTIVE_USERS;
DEALLOCATE INACTIVE_USERS;
PRINT 'Count: ' + CAST(@UserCount AS VARCHAR);
END;
Created: 2.1.2017
Cleanup TSQL Scripts
