I need to create a logon trigger where I am able to capture both Windows and SQL Server authentication account users last login time. I am running into the issue of when I log in as a SQL Auth user with public server role permissions only and I get:
Logon failed for login ‘xxx’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
I need to be able to allow users who only have public server-roles to login and track their time. I cannot grant them with any other permissions except for public.
This is my current trigger:
CREATE TRIGGER LogonTimeStamp
ON ALL SERVER FOR LOGON
AS
BEGIN
DECLARE @time DATETIME
SET @time = (SELECT login_time FROM (SELECT login_name, login_time, RANK() OVER(PARTITION BY login_name ORDER BY login_time DESC) AS rnk
FROM sys.dm_exec_sessions) ds WHERE rnk = 1 AND login_name = SYSTEM_USER)
IF EXISTS (SELECT * FROM db_admin.dbo.tblUser WHERE name = SYSTEM_USER)
UPDATE db_admin.dbo.tblUser
SET lastLoginDate = @time
WHERE name = SYSTEM_USER
END;
NOTE: This request comes from someone who has something similar in place on Oracle and not SQL Server.
TLew79 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
12
You can use the SQL Server log event to do so. Just change the default behaviour to trace all login, failed and successful :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'AuditLevel', REG_DWORD, 3
GO
Use the special procedure xp_readerrorlog to capture those event in a scheduled way by time interval :
EXEC xp_readerrorlog 0, 1, N'Login succeeded for user', N'Connection made using', '20240901 00:00:00', '20240901 23:59:59.997';
This give you the login for 1 hour on september, 1 of 2024…
Of course you can store this result in a table, parse the data of the message and find the last logon… The name of the login is just after the first quote and the following quote
8