SQLServer : disable inactive logins

SQLServer doesn’t have a functionality to expire accounts if they have not been used for a certain amount of time. Sometimes this can come in handy, e.g. in an environment you inherited without documentation or knowledge about the different users connecting. Disabling these “unclear” accounts immediately might lead to significant access problems for users or applications that rightfully only connect e.g. once a week.

The following articles describes an implementation of an expiry mechanism. Logins that are not used for 2 months will be disabled. For this to work, I’ll use a AFTER LOGON trigger to keep track of the opened connections in a table and a SQL Agent job to verify the access on daily basis.

History Table

First, I’ll create a simple table that will store each successful login attempt.

CREATE TABLE AXIS0.[dbo].[Logins](
	[Login] [nvarchar](100) NULL,  -- the server principal that successfully logged in
	[Login_Date] [datetime] NULL,  -- the date at which the principal logged in
	[Login_Database] [nvarchar](100) NULL  -- extra information, like the database the principal logged in to
)

Logon Trigger

The logon trigger fires after the authentication phase, but before the session is established. It is stored at instance level in Server Objects, Triggers. Let’s create a Logon Trigger at instance level to INSERT a record in the Logins table every time a user logs in.
Note: you may want to filter this to specific accounts, as the table can grow big for known frequently connecting applciation accounts.

CREATE TRIGGER [tal_track_logins]
ON ALL SERVER
  WITH EXECUTE AS 'AXIS0\dba' 
AFTER LOGON 
AS
BEGIN
    INSERT INTO AXIS0.dbo.[Logins] ([Login], [Login_Date], [Login_Database]) 
    VALUES  (ORIGINAL_LOGIN(), GETDATE(), ORIGINAL_DB_NAME());
END;

Execute As specifies an account that has the rights to perform the actions listed in the body of the trigger. In this case, it means the right to INSERT into the Logins table. For all specifications, make sure to visit the above link to Logon Trigger documentation.

Disable logins stored procedure

Finally, we’ll need logic to disable the logins that haven’t been used for a while. This is encapsulated in a stored procedure for easy maintenance. The procedure will

• retrieve logins last seen 2 months ago
• To make sure we don’t disable special accounts we filter those out
• To make sure we don’t disable an already disabled account, we link with the server_principals view to filter out only enabled accounts
• For each of those logins, it will execute dynamic sql to disable the login

CREATE PROCEDURE sp_disable_inactive_logins AS
BEGIN
  DECLARE @login NVARCHAR(100)	
  DECLARE inactive_logins CURSOR FAST_FORWARD READ_ONLY FOR 
  SELECT [Login]
    FROM DBA.dbo.Logins lh INNER JOIN sys.server_principals l ON lh.Login = l.name
   WHERE [Login] NOT IN ('AXIS0\admin', 'AXIS0\backup') -- never lock fixed accounts
     AND [Login] NOT LIKE 'NET Service%'  -- never lock service accounts
     AND [Login] NOT LIKE 'AXIS0\app-%'   -- never lock application accounts
     AND l.is_disabled = 0
   GROUP BY [Login]
  HAVING DATEDIFF(M, MAX(Login_Date), GETDATE()) >= 2;

  OPEN inactive_logins
  FETCH NEXT FROM inactive_logins INTO @login
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC( 'ALTER LOGIN ' + @login + ' DISABLE;');
    FETCH NEXT FROM inactive_logins INTO @login
  END

  CLOSE inactive_logins
  DEALLOCATE inactive_logins
END

Make sure to adapt the filtering to your environment.
You can insert an initial record for accounts you’re not sure if they ever login.

Note: Logon triggers are very powerful. When implemented incorrectly, they can stop you from connecting to your database.

Verification Job

To automate the expiry, we have to run the stored procedure daily. Using SQL Agent, we will run the above stored procedure on a daily basis, and this will maintain the inactive logins going forward.

For this, create a new job, with 1 TSQL Job step. In the Schedules, create 1 schedule with a daily recurrence.

Conclusion

The above setup allows you to follow up on the login activity. Using an After Logon trigger keep a history of sessions. Use a SQL job to daily check on accounts that expire.

Read More

If you’re interested in SQLServer, don’t miss this article. It explains how to create a nice overview of your SQL Agent job runs