How to be Big Brother in Microsoft SQL Server!!!

A few years ago I was working as a SQL Developer at a large company in Sydney’s CBD. Although an established business, the environment was relaxed and based on many of the “start-up” type businesses which you see around today. For example the office was filled with comfortable lounge chairs and TVs everywhere, plus an on-site barista and cafe.

Of the roughly 120 staff in the Sydney office, over 100 would have been in IT, with probably 20 teams spread across everything from mobile web and app development, to myself in the database team and 3 or 4 full-time SQL DBAs.

As one of only a handful of people who had access to the production SQL environment, and the ability to view and change the production data, I would often get tickets in regards to importing or exporting data.

One day a request came in for me to import some data from Excel, and then use it to update some records.

To assist with this, I used the SQL Server Import And Export Wizard to import this data from Excel into a new table.

Within minutes of me doing this, my phone rang. It was the senior DBA. Ie, the man who all the database programmers and DBAs reported to.

His question was simple … “why did you just create a new table on a production database?”.

Even though I had all the boxes ticked from the testing team, and another senior manager (for which this task required) ... for whatever reason he didn’t see the authorisation and wanted to know what I was doing. When I explained it to him, he approved it and had no issue with what I was doing. But … it got me thinking … how did he know?

Not long after, I’m at a smaller company and am THE DBA when I notice that a .net programmer had snuck in some DDL changes into a release script. Once released into production, it caused some issues that did not appear in UAT. It was a lesson for me in that I needed to performance test the changes that came through via the developers.

As it was a smaller company (especially in terms of IT resources), I now wanted to keep more of an eye on what the .Net programmers were doing. And it reminded me of what happened to me earlier at my previous role - how did my boss know I made DDL changes almost immediately?

I soon discovered that you can setup what is known as Server Triggers.

There are many examples on sites such as stack overflow on how to set these up, so I won’t bother with typing the exact steps. But in short all you do is enable SQL Mail, and then create Server Triggers which are fired from the system tables. From the event which is fired, call a stored procedure which then writes the changes to an audit log (along with who did it, assuming you're using Windows Authentication) and also sends you an email.

Simply do this on whichever SQL Instance you wish to monitor and you too can play Big Brother and keep an eye on any cowboy developers! Personally, from now on I always do this on all UAT and Production instances. Often the Dev instances have too many changes made and I feel that if I get too many emails coming through saying “DDL change” then they become lost in my inbox of other alerts. But when I get one saying change(s) in UAT or production, then I can investigate … and if necessary get out the proverbial big stick!