T-SQL Tuesday #106 - Triggers

2018-09-11 0 Comments

Dave Mason T-SQL Tuesday

For T-SQL Tuesday #106, Steve Jones (b|t) asks us to write about an experience with triggers, either good or bad. I'd bet that most of you are thinking about INSERT, UPDATE, and DELETE triggers. But for my DBA sensibilities, the best trigger is a DDL trigger.

How many times have you seen someone online looking for help because something went missing? Maybe it was a table, an index, or (gasp!) a database! And after the fact, they're looking for answers that, by now, are hard to come by. Missing and altered objects can be a bad thing. Compounding this is another reality that's equally bad: no matter how loudly we scream, we can't get the rest of the world to agree to get on board with the Principle of Least Privilege. DDL triggers give us a way to audit certain events, and even prevent them, if necessary. (If this sounds like something you're interested, here's another post that can help get you started.)

As for me, here are some of the key DDL events I create triggers for:

  • CREATE_DATABASE: What's this new database for? What recover model is warranted? How often do I need to back it up? Who needs access to it?
  • ALTER_DATABASE: Recovery model, compatibility level, auto close/shrink/update stats, etc. I want to know when these settings get changed.
  • DROP_DATABASE and DROP_TABLE: These usually get rolled back with a stern message if it's in a production environment.
  • DDL_GDR_DATABASE_EVENTS, DDL_APPLICATION_ROLE_EVENTS, DDL_USER_EVENTS, DDL_LOGIN_EVENTS, ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER, et al: keep a sharp eye on authentication and authorization!
  • ALTER_INSTANCE: this is a biggie. You might be surprised how much a vendor's application installation messes with your instance.

Arguably, working with DDL triggers can be challenging. There is some XML involved by way of the EVENTDATA function. But the payoff of learning DDL triggers can be immense. If you are managing even a single instance of SQL Server, I'd encourage you to take a look. Happy T-SQL Tuesday, everyone!

0 comments: