Each database administrator wants to detect and delete unused logins. By default in SQL Server, the last time the login was logged is not stored in a table or dmw. To meet this need, we will use the following script.
First of all, you should create a table in any database with the following script. I chose the master database, because its my local PC. You should create another database for this in your environement. In this table we will store the name of the login and the date of the last login.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
use master create table mylogins( login_name nvarchar(max), login_time datetime ) --With the following script, we get the names of the logins currently logged in and the last login dates. SELECT login_name as[login_name],MAX(login_time)as [login_time] into #session FROM sys.dm_exec_sessions GROUP BY login_name; --We transfer the previous date information to the table we created in the master database. MERGE INTO [master].dbo.mylogins WITH (HOLDLOCK) AS target USING #session AS source ON target.login_name = source.login_name --If there is this login in the table in the master database, we are updating the last login date. WHEN MATCHED THEN UPDATE SET target.login_time = source.login_time --If the login name is not in the table in the master database,it performs insert operation. WHEN NOT MATCHED BY TARGET THEN INSERT (login_name, login_time) VALUES (source.login_name, source.login_time); DROP TABLE #session |
You can create the above script as a job that runs at 1 minute intervals. You can then use the following script to query which login is active and which login is not used.
|
1 |
select * from mylogins order by login_time asc |
