revisiting an old issue, logon trigger blocking logons

Question

revisiting an old issue, logon trigger blocking logons

Glen_A

SSC Eights!

Points: 988
More actions
April 16, 2019 at 7:07 pm

#3415867
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
AS
BEGIN
if
( (ORIGINAL_LOGIN() <> 'sa')
and APP_NAME() not like 'SQLAgent%')
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()
END
GO
```
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 5 years ago by Glen_A.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

The principals would still need access/connect permissions to the database, not just the insert permissions from the public role. The trigger will still execute under the login's permissions. When you didn't add the users to the database that had the audit table, perhaps you had enabled the guest account in that database?

Sue

Glen_A SSC Eights! Points: 988 More actions · Answer 2

Thanks for the reply

Yes I added the principals to the audit database and everything is working now.

The guest account is disabled on the other database, but there were non domain users that used the main database that were added when I checked it out further, so I did the same with this one. On the 2008 database I had this running on before I must have already added the principals when I put it in place the first time.

I think what happened was the DBA that did the move / upgrade from 2008 to 2014 just brought the one database across and not the AuditDb database, and when he realized there was no audit records he used the script I had written that created the AuditDb database and trigger and just ran them, which did not add the users to the database.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Yes, sounds like that is likely what the DBA did...those dang DBAs are always messing things up 🙂 I think that's one of the reasons that people often have examples of this with the table in master. It addresses the permissions (guest is enabled in master) and eliminates the need to remember to add logins to the auditing database. But having user objects in master isn't always the best idea either and I don't like to enable guest in user databases so I'm not sure which way I'd personally prefer.

Thanks for posting back - glad you have it all working now!

Sue

Glen_A SSC Eights! Points: 988 More actions · Answer 4

I thought about putting the table in master but to me that's akin to putting a table in system tablespace in Oracle, something I just won't do.

When I did this same thing in Oracle I just created a table in it's own (AudLog) schema, then logon (and logoff) triggers, which are created as sys owner & that was it. MSSQL is quite a bit more complex in how it handles users etc.