Post reply

ASPNETDB - Lock Out SharePoint Accounts not logged on in a long time

  • August 27, 2021 at 10:16 am

    #3924422

    Hi Guys,

    I'm not a DBA so looking for assistance with this one.

    We need to automate locking out accounts that haven't logged on to the SP site in over 12 months.

    Authentication method is FBA. SQL server is 2012 with aspnetdb.

    I've run the below just to get a feel of which details to use. However, is there a way we can run a scheduled job in SSMS to be checking 'LastLoginDate' then lock the account?

    SELECT UserName, CreateDate, LastLoginDate, LastActivityDate, LastLockoutDate, IsApproved, IsLockedOut

    FROM aspnet_membership JOIN aspnet_users ON aspnet_membership.userid = aspnet_users.userid

  • August 27, 2021 at 2:17 pm

    #3924504

    I think this is possible but messy and mildly risky.  Mildly risky as you would need to use dynamic SQL and there is a chance you could lock out an account that doesn't use SharePoint all that often or any service accounts which may not perform an actual login.

    If it was me, I would use the query you have (or something similar), but put a WHERE clause on it to look at the LastLoginDate within the past 12 months.

    As I expect that this list wouldn't be too large, instead of having it automatically clean it up, I would have it email the SharePoint admins with a list of users who haven't logged in in the past year and the SharePoint admins can decide if the account should be disabled or not.

    The other fun thing is if you do this as a job and you update SharePoint, you will need to test that it properly locks out the accounts as an update to the system MAY change how it handles locking accounts.  If it is more of a report for the admins, then worst case with the update, the alerts stop going out and you now need to fix the job so the admins can administer the system properly.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply