While looking at audit reports I noticed that one database I had long ago implemented a logon trigger & table to track logons on was no longer populating the table. The database had been moved to a new server awhile ago (not by me) and the DBA who moved it had created the audit database & table, and created the same trigger, which is:
CREATE TRIGGER [Tr_ServerLogon]
ON ALL SERVER FOR LOGON
( (ORIGINAL_LOGIN() <> 'sa')
and APP_NAME() not like 'SQLAgent%')
INSERT INTO AuditDb.dbo.ServerLogonHistory
The trigger was disabled, so after checking to make sure insert was granted to public on the AuditDb.dbo.ServerLogonHistory table, I created a test user, enabled the trigger, tried to logon from another session and the trigger blocked the logon. Now I know why the trigger was disabled.
I have the identical setup on another database (2008) and the logon trigger works fine. It is setup almost identical, but I see the AuditDb database has specific users from the other databases added as users. I am trying to remember if I need to add all the database users specifically to the AuditDb database & give them access to insert into this audit table. There are domain users and a couple of non domain users on the server I want to audit.
I added the test user to the AuditDb database and gave it explicit access to the table in securables, re enabled the trigger and was able to log in ok. This is puzzling to me as I didn't think I would need to with insert granted to public on the table. I then did the same with domain_name\domain_users login and I was able to log in with windows authentication ok. Interestingly, on the other database this trigger works on this is not done with domain_name\domain_users added explicitly.
I remember going through this same thing years ago and don't recall exactly what I did - there's a long thread on it but of all my topics here it's the only one I can't retrieve for some strange reason, I get a 404 error.
thanks in advance.
- This topic was modified 4 months, 1 week ago by Glen_A.