How to identify inactive SQL logins in SQL 2005/2008

Posted on 2011-10-21
Last Modified: 2012-05-12

Is there a way to identify inactive SQL Logins?
By "Inactive", I mean logins that have not been used for a certain period of time, say one month or longer.

Thank you for your help.
Question by:JOSHUABT
    LVL 8

    Expert Comment

    by:Som Tripathi
    Below might help you -

    select spid [SPID] , datediff(hh, login_time , getdate()) [HOURS_OLD] , * from master..sysprocesses
    where datediff(hh, login_time , getdate()) > 48  and
    lower(status)  = 'sleeping' and     upper(cmd) in ('AWAITING COMMAND','LAZY WRITER','CHECKPOINT SLEEP')

    I expect that any connection longer than 48 hours can be said as inactive.
    You may increase number of hours as per your requirement.

    Author Comment

    Thank you for your response.
    My definition of "inactive users" might not have been clear enough.
    I want to identify those SQL logins that have not been used at all for a certain period of times ( say, one month or one year ).
    LVL 8

    Expert Comment

    by:Som Tripathi
    Sorry, there is no clear method in SQL Server to find this.
    LVL 26

    Assisted Solution

    In SQL server there is an option called Longing audit which by default is setup to "Failed login only". What this means is that any unsuccessful attempt to login will be recorded in the SQL log. You can change that setting to None, Successful only or Both failed and successful:

    In Management Studio Obect Explorer right click on the server name > Properties > Security and choose "Both fail and successful" logins > Click OK.. Restart the SQL server, you have to do this in order the change to take effect.

    Now you will be able to query the SQL log and see what logins are still used. You will have to wait, of course, some time to gather data after this change.
    LVL 13

    Accepted Solution

    Another option is to create logon trigger ( and store login name/time of login in the table. Next, you would be able to query that table and compare it with sys.server_principals, for example. This could be easier than go through the  sql log.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL .Write 3 35
    What is ISQL? 6 84
    ms sql + top 1 for each customer 3 22
    Delete from table 6 26
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now