Audit
Get notified when a user with administrative privileges logs into your SQL Server
I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.
Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.
Auditing your SQL Servers
Below is what I recommend to my clients to evaluate the security of their infrastructure quarterly, although more frequently is always better. Typically with this set of scripts, you’ll want to either compile the data into workbooks, flat files, or at least historic tables so you can track trends and changes.
We begin with two queries; one brings back the SQL Server Name and Instance so we know what we are evaluating and then we check the version of SQL Server which in itself has its own security repercussions based on service pack and year.
Who changed my data?
Odds are that if you are reading this; someone, somewhere, somehow… has changed your data. Odds are also, you may be out of luck figuring out what changed and who did it. The good news is that the lesson has been learned and you can take action now to prevent this in the future.
SQL Server by default does not automatically track who does what to your tables. It does track the made to your database, but it won’t tell you “who done it?”. You can however use the transaction logs with extreme painstaking patience to reveal what changes were made.