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.
create table mylogins(
--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]
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.
select * from mylogins order by login_time asc