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.<\/p>\n
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.<\/p>\n
use master\ncreate table mylogins(\nlogin_name nvarchar(max),\nlogin_time datetime\n)\n\n\n--With the following script, we get the names of the logins currently logged in and the last login dates.\nSELECT login_name as[login_name],MAX(login_time)as [login_time]\ninto #session \nFROM sys.dm_exec_sessions\nGROUP BY login_name;\n\n--We transfer the previous date information to the table we created in the master database.\nMERGE INTO [master].dbo.mylogins WITH (HOLDLOCK) AS target\nUSING #session AS source\n ON target.login_name = source.login_name\n\n--If there is this login in the table in the master database, we are updating the last login date.\nWHEN MATCHED THEN \n UPDATE SET target.login_time = source.login_time\n\n--If the login name is not in the table in the master database,it performs insert operation.\nWHEN NOT MATCHED BY TARGET THEN\n INSERT (login_name, login_time)\n VALUES (source.login_name, source.login_time);\nDROP TABLE #session\n<\/pre>\nYou 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.<\/p>\n
select * from mylogins order by login_time asc<\/pre>\n\n<\/div>\n