Here is my entry for this month’s T-SQL Tuesday.
I once had to some auditing for a customer and it was a complicated, multi-stage process. We had to be able to demonstrate who had admin access and what kind of activity was going on, on the server. But before we could do any of that, we first had to identify who was actually logging on.
Triggers to the rescue
So what are the different options for telling who is logging on to a a SQL server? 5 options come to mind:
- Configure login auditing.
- Login Trace
- Login Extended Event Session
- SQL Audit
- Server Trigger
So going through each one of them:
Configuring login auditing really isn’t a good solution. What you are doing is changing the base settings to log successful logins in addition to failed logins. The problem is that these events are written to the SQL Server event log, which isn’t convenient to parse.
Well what about using a trace? Well I’ve always been told that traces are expensive in terms of performance so I shied away from using one of those. In retrospect, I doubt it would have been too expensive since it’s only tracing logins. If anyone knows, let me know!
The next option is to use Extended Events, which often have better performance. Unfortunately, this server was SQL Server 2008 R2 and there was no GUI support for extended events. So that wasn’t ideal.
What about SQL Audit? Underneath the hood, SQL Audit is just Extended Events. That being said, there is at least some GUI component to it. For 2008R2, it required Extended Edition. while that wasn’t an issue for us, it seemed like overkill.
So what’s the last option? Creating a server level trigger. This was simple to implement and easy to dump the data into a SQL table for reporting purposes.
Proceed with caution
So, what’s the downside. Wellllllll. What happens if you have an error in your code? If you hit an error, then you can’t login. At all. Anyone.
There are ways to resolve this issue, but it requires shutting down the SQL Server and taking an outage to fix it. Suffice it to say, I spent a looooot of time testing before I pushed this out to production.
Overall, triggers provided a simple solution to a simple problem. But the solution required a good dose of caution.
