How to track inactive logins in SQL SERVER

  • [アーティクル]

Many customer asked me if there is any easy way to find the list of logins which are inactive and can be deleted hence thought of documenting this method using blog.

You could go to SQL SERVER Property and then turn on “Successful logins only” to record login information in SQL SERVER ERRORLOG.

It would be something like this:

 

After that you have to restart the SQL SERVER Service.

Post restart if you read the SQL SERVER ERRORLOG then you will see entries like this:

/*****************************************************************/
2014-07-31 16:52:07.010 Logon Login succeeded for user ‘domain\name'. Connection made using Windows authentication. [CLIENT: 127.0.0.0]
2014-07-31 16:52:10.050 Logon Login succeeded for user 'SQLAUTHENTICATION'. Connection made using Windows authentication. [CLIENT: 127.0.0.0]After that you could create the following table and then read the sql

/*****************************************************************/

 

Now to track all active logins you can go ahead create "Track_login" table and then read the sql server errorlog for “Login succeeded for user” string using following queries and then import this data in TRACK_LOGIN table

/*****************************************************************/

 USE TEMPDB

GO

CREATE TABLE TRACK_LOGIN

(

LOGONTIME DATETIME,

LOGON VARCHAR(15),

LOGINNAME VARCHAR(MAX)

)

GO

INSERT INTO TRACK_LOGIN EXEC MASTER.SYS.XP_READERRORLOG 0, 1,'LOGIN SUCCEEDED FOR USER'

/*****************************************************************/

 

  Note: Above query will read the current SQL SERVER ERRORLOG and then insert the Logon related events in "Track_Login" table. If you want to read older errorlog file then you could change the xp_readerrorlog first parameter.

You could collect the data for month or week time as per your business requirement and then find out the active login information by running following query against TRACK_LOGIN table

/*****************************************************************/

SELECT DISTINCT SUBSTRING(LOGINNAME,PATINDEX('%''%',LOGINNAME)+1,PATINDEX('%.%',LOGINNAME)-PATINDEX('%''%',LOGINNAME)-2)FROM TRACK_LOGIN

/*****************************************************************/

You could join above query output with SYS.SYSLOGINS system table to find out the inactive logins information.

I will also recommend disabling the logins first before you drop them to make sure this does not impact your applications and later you could drop them.

 

ALTER LOGIN [TEST] DISABLE
 

There are other ways also to find out inactive logins information for example you could use profiler trace, Extended Events and SQL SERVER Logon trigger. However these methods are expensive in terms of resources hence I like above method.

I will try to document others methods as well in my coming blogs. I hope this will help and as always please test this before you use this on your production SQL SERVER.

  

Vikas Rana, Technical Advisor

Microsoft India GTSC

Comments

  • Anonymous
    August 02, 2014
    the extended proc should be in the schema sys not DBO.

  • Anonymous
    August 06, 2014
    The title of this article is pretty misleading, since you're not "tracking" inactive logins at all - only EXCEPTing active logins from all logins.