Skip to content

jstexasdba Posts

Octopus and a reason to use DDL Triggers

I came across an environment recently that had multiple domains with particular trust levels between the them. I then, found out, that when deploying projects via Octopus, there was a project step that was creating a windows login from one domain to another because Octopus had admin permissions across the board (Oh yes, I already know what you are thinking). My initial thought was to contact network team and get an explanation on why the trust levels, however what would that really solve for the immediate need? I then wanted to investigate all the permissions Octopus should truly have, however I did not have time to investigate all of the appropriate permission that would be required. I thought, what a perfect chance to start my own personal challenge of #100daysofcode from twitter.

How could I protect the SQL environment in each domain to ensure that never happens again? How can I ensure that when deploying via Octopus, the individual doing the deployment can be notified of the failure in a clear and concise way? I then stepped into a whelm that I have not touched in quite sometime, triggers, yes I said it, triggers. More specifically DDL triggers. I have had multiple reasons why I have not had to deal with triggers too much, but finally I had enough of this and had to put my foot down as results wanted, were not being driven.

What can DDL triggers do for you? DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements and stored procedures that perform DDL-like operations. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.

I currently utilize extended events for a lot of auditing and these items are always recorded and emailed the following morning for audit review. I finally got tired of waiting and wanted an email alert immediately, of when this particular event occurred and want to ensure that Octopus also recorded the alert to notify the individual doing the deployment.

Here is the script I came up with:

This is what Octopus looks like:

Leave a Comment