A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Thank you very much Erland!
This is exactly what I was looking for!
--Dan
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi All,
I have a requirement to create a LOGON TRIGGER to monitor logins for auditing purposes. The requirement is to create a table and capture all known logins. I am using SQL Server 2017, enterprise edition.
I'm curious which database I should use to store this information. If login attempts are made before my user database is online, how does the trigger behave?
My trigger is designed to only monitor sys.dm_exec_sessions.is_user_process = 1 processes, and capture certain identifying information. At the moment, I am storing the results in a table I created in master.
All inserts/updates are wrapped inside of TRY/CATCH logic
Which database should I use to create the audit table?
Thank you!
--Dan
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Additional SQL Server features and topics not covered by specific categories
Thank you very much Erland!
This is exactly what I was looking for!
--Dan
Thank you Erland,
I am also finding that because I am reference sys.dm_exec_sessions and sys.dm_exec_connections I am running into VIEW SERVER STATE permission problems.
If I implement your example, should I assign "VIEW SERVER STATE" permission to 'LoginTriggerUser'?
Thanks again!
--Dan
If you would log to a different database than master, and that database has not yet become available, logins will fail.
To deal with the permissions issue, you should create a special login for the login trigger:
CREATE LOGIN LoginTriggerUser WITH PASSWORD = 'WhateverYouSeeFancy'
ALTER LOGIN LoginTriggerUser DISABLE
GRANT INSERT ON AuditTable TO LoginTriggerUser
Then you create the trigger with the EXECUTE AS clause:
CREATE TRIGGER logintri ON ALL SERVER WITH EXECUTE AS 'LoginTriggerUser' FOR LOGON AS
To get the name of the user logging in, you need to use the function original_login(). SYSTEM_USER and similar function will return LoginTriggerUser.
I also have some text about logon triggers here: http://www.sommarskog.se/grantperm.html#serverlevelobjects (although here logging is done to a separate database).
Hi GuoxiongYuan-7218,
Thank you for your response. This is what I was thinking as well.
One other question: For some users, my trigger is properly logging the information to the table, but not for all users. In some cases, it is preventing them from logging into SQL Server! (This is still a development project!).
What privs do I need to assign to my users, target table, and trigger in order to get this to work. Prior to installing the trigger, all users were able to connect to the server successfully.
Thank you!
--Dan
I would suggest to create the audit table in the master database since it always exists if the SQL server is online.