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.
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 )
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.
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.
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.
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
Nice job on this. I made a change to your trigger logic to only record SQL login accounts and to ignore all other types since those were the only ones I wanted to monitor. Here is the trigger code I am using:
CREATE TRIGGER [audit_track_logins]
ON ALL SERVER
WITH EXECUTE AS ‘sql_audit’
INSERT INTO [Production Support].[dbo].[Logins] ([Login], [Login_Date], [Login_Database])
sp.name = ORIGINAL_LOGIN()
sp.type IN (‘S’) — Only SQL login accounts, AD accounts are marked with a ‘U’ and are managed within AD
Thanks Jeff, I only saw your comment now! Indeed, that’s a good addition. Glad it helped you