How to identify inactive SQL logins in SQL 2005/2008

Posted on 2011-10-21
Medium Priority
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

Expert Comment

by:Som Tripathi
ID: 37010116
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

ID: 37010145
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 ).

Expert Comment

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

Assisted Solution

Zberteoc earned 400 total points
ID: 37011005
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

dwkor earned 1600 total points
ID: 37011574
Another option is to create logon trigger (http://msdn.microsoft.com/en-us/library/bb326598.aspx) 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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