Inspired by a brief conversation in the #CodingBlocks community Slack: A short discussion and example of a “who-dunnit” (“who done it”, a colloquialism for a murder-mystery type thing) trigger, to find how what user is doing deletions against a certain table.
The Background Check
Let’s name our hypothetical database
CSI. In it, we have a table,
dbo.Victims, where it seems like data is being randomly deleted at random times. As we all know, this is impossible — computers never do anything truly randomly, much less RDBMSes.
Insert witty counter-example here. You know you have one.
So we want to find out who’s doing these deletions. One DBA says, “Hey I got an idea… Let’s put an
after delete trigger on the table!” Another DBA says “I abhor triggers; let’s log
sp_WhoIsActive every 5 seconds to try to catch the suspect ‘in-the-act’.”
Both approaches have their merits, and neither is that uncommon. However, the latter is much more regularly blogged about, so I’m going to present the former, because it kinda helped remind me of a few things that I hadn’t used in a while. I’d also argue that the latter is much less of a “guaranteed capture”, since you’re gambling pretty liberally on the fact that the
delete transaction will even last that long; it’s statistically more likely that you’ll miss it.
Here’s a SQL snippet that shows a basic
after delete trigger created on our
dbo.Victims table. Notice the use of the special
Deleted table reference — this is a “temporary, memory-resident” table according to the Docs, and it holds all the records that were/are-about-to-be deleted from the target table.
I feel like it used be called a “temporal table”, but that now refers to a new feature in 2016, where SQL keeps a hidden history-tracking copy of your table that you can reference like a time-machine; which, incidentally, almost* negates the need for such things as these triggers we’re talking about, but that’s another topic for another time.
*(The ‘almost’ is because temporal tables don’t tell you “WHO”, which is our primary motivator here.)
The interesting bits are how we identify our suspect, our ‘killer’ if you will. See, we not only want to know who they are in the database context, we also (and likely, more importantly) want to know who they are at the server level context. And just in case they’re impersonating another login, we want to check that too.
So we actually have a lot of options here. There’s
USER_NAME(), for the DB context user. Then we have
ORIGINAL_LOGIN() for the server context. If you’re curious, you could also get things like
@@SPID (server session id),
SUSER_ID() (server login id), and
SESSION_USER (database session user).
ORIGINAL_LOGIN() may be the most potentially interesting, especially if we want to write our trigger with elevated (impersonated) permissions to be able to write to the logging table that we’ve set up to capture its detective-work. I did not need it for this example, but it’s worth keeping in mind.
So we’ve got our evidence table, we’ve got our detective trigger, now we just need a suspect. Thankfully we can test it out first, to make sure our operation will succeed when the real perp comes along. We can do this, of course, by impersonation. Or by using different SSMS query-windows with different logins — your choice.
Our faux-suspect’s login name is
DummySuspect. We map him to the
db_datareader roles in our
CSI database — we know the real perp at least has
write permission on the table
dbo.Victims, otherwise he/she wouldn’t be able to delete those poor victim rows! And we’re probably the
db_owner, which is fine. Let’s call our own login
Now we can pretend to be
DummySuspect and execute a
CSI.dbo.Victims , and make sure it writes to our auditing table, which we called
Yes, in practice, we’d probably want to put our
Evidencetable in a separate database, to really ensure those pesky Suspects can’t update it or delete from it, i.e. “cover their tracks” — here, I’ve settled for simply using a different
schema, to keep the example workable. Otherwise we’d have to deal with cross-DB permissions and such, which goes beyond the scope of one little blog post.
Ready? Let’s try it!
Go on over to the GitHub repo and check out the code. There are 3 easy steps – ‘Step 1’, create the objects, including a new DB to house them called
CSI. You’ll see the trigger in there as well. Then you can try ‘Step 2’, where I impersonate
DummySuspect and delete a row from
Victims, and then check the
Evidence log when done. And finally, ‘Step 3’ is a similar test, but assumes that you’ve actually connected that SSMS window/tab/query AS
DummySuspect instead of impersonating him (or her!). After you’ve done that, check out
aud.Evidence again to make sure it logged the 2nd delete.
And there you have it. A simple example of how to write and test an
after delete trigger that writes the action info to a separate auditing table for investigation.
Hope you enjoyed! Leave a comment here or on GitHub; I welcome all feedback.
One more thing…
Apparently I’ve been
fork‘d! Someone on GitHub liked my take on the Nested Set Model so much that they decided to pull it into their own library and have a play with it. Yay!! 😀 Whoever you are, thank you and good luck building with it. Enjoy!