Best Practice: which database should I use when creating an audit table for a SQL SERVER LOGON TRIGGER

Question

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

Answer 1

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).

Answer 2

I would suggest to create the audit table in the master database since it always exists if the SQL server is online.

Answer 3

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

Answer 4

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

Answer 5

Thank you very much Erland!

This is exactly what I was looking for!

--Dan