List of user logging into SQLServer database

Question

Need to find the list of SQLserver users logging into the database server for last 2 weeks. Found this script but since I restarted the server it lists todays login date:

SELECT UNPVT.[DataBase],
MaxLastUse = MAX(UNPVT.MaxLastUse)
INTO #tempdatabases
FROM
(
SELECT [DataBase],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
(
SELECT [DataBase] = DB.[name],
last_user_seek = MAX(IU.last_user_seek),
last_user_scan = MAX(IU.last_user_scan),
last_user_lookup = MAX(IU.last_user_lookup),
last_user_update = MAX(IU.last_user_update)
FROM sys.databases AS DB
INNER JOIN sys.dm_db_index_usage_stats AS IU ON IU.database_id = DB.database_id
GROUP BY DB.[name]
) AS DBIU
) AS P UNPIVOT(MaxLastUse FOR ColumnName IN(last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update)) AS UNPVT
GROUP BY UNPVT.[DataBase]
HAVING MAX(UNPVT.MaxLastUse) IS NOT NULL
ORDER BY MAX(UNPVT.MaxLastUse) ASC;
SELECT name,
create_date AS 'Created on',
'Not used since last instance start',
(
SELECT create_date
FROM sys.databases
WHERE database_id = 2
) AS 'Instance start date'
FROM sys.databases
WHERE database_id NOT IN(1, 2, 3, 4)
AND name NOT IN
(
SELECT [DataBase]
FROM #tempdatabases
);
DROP TABLE #tempdatabases;

Also activated both failed and successful logins in sqlserver. Do not see anything in audit. Followed this link https://learn.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio?redirectedfrom=MSDN&view=sql-server-ver15

Answer 1

Hi @Avyayah ,

Configure Login Auditing in SSMS, this list the logins in server level(instance level). After you enable the audit in SSMS, you need to restart the SQL server instance to make this change effect. You can get the result in error log as below screenshot in my environment.

> Found this script but since I restarted the server it lists todays login date

The information in the DMV will be cleared and nulled whenever you restart SQL Server.

From my knowledge, SQL server audit could not audit database level login user. If you want to get the last time SQL databases were accessed. Please using below T-SQL. Please note these values reset after SQL server restart.

; WITH STATS as (  
SELECT D.name, D.database_id  
, max(last_user_seek) as last_user_seek  
, max(last_user_scan) as last_user_scan  
, max(last_user_lookup) as last_user_lookup  
, max(last_user_update) as last_user_update  
FROM sys.databases D  
LEFT OUTER JOIN sys.dm_db_index_usage_stats S on D.database_id = S.database_id  
WHERE D.database_id > 4  
 AND S.object_id > 100  
GROUP by D.name, D.database_id  
)  
SELECT * from STATS  
UNION  
SELECT D.name, D.database_id, NULL, NULL, NULL, NULL  
 FROM sys.databases D  
WHERE D.database_id > 4  
  AND not exists (select * from STATS where name = D.name)  
ORDER by 1  

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.