Voice of the DBA

Tag: auditing

Vibe Coding a Login Tracking System
A customer was asking about tracking logins and logouts in Redgate Monitor. We don’t do this natively, as this really needs an XEvent session. I decided to see if an AI could help me get a solution setup that might let me build a custom metric to track this.

I could do this myself, but it’s some looking syntax and capabilities, futzing with different code items, and trying to think about options. The goal here is can an AI help and save time. Not do the work for me, but assist.

So maybe not Vibe coding per se, but felt like I did little.

Video Walkthrough at the bottom.

This is part of a series of experiments with AI systems.

Note: This isn’t something I necessarily worry about. The rate might tell me if I’m under attack, but I’d hope applications would detect this first (and be able to block things)

The Problem

The customer just asked if they could track logins and logouts. I mentioned the Server Properties (shown below), to see what they’d done. They hadn’t used this, but also, it’s not very flexible or reportable as it puts info in the error log.

Tracking this info really requires an XE (Extended Events) session. That’s a lightweight way to capture this information. If I want to capture some info about the client logging in, or failing to log in, that’s the way.

A separate request was could we also get logouts. The only way to do this is with an XE session and the sqlserver.logout event.

With that in mind, let’s see how my assistant can help.

Using Claude

I opened Claude and asked this: “in sql server can I track login counts and logout counts from t-sql?” and actually spelled everything correctly. No savings here.

The base answer I got started like this, giving me a few options.

The ending asked me if I’d like to get the Extended Event option. It had provided only the login trigger option. I need an assistant, so I said yes.

That first answer was maybe 20-30 sec, but I started reading things, so this felt like a discussion with another DBA. Once this started, I let it go and it started to write out and work on code on the right, and then fill in the results on the left. This was a few minutes, so I flipped over to answer a few emails while I saw this on another monitor.

The results started with a table to store data and then an XE session. The whole page looks like this, which is a lot.

Here’s the full left side. Notice that it asks me for a next step. I’ve met a lot of junior DBAs, or even Senior-DBAs-with-1-year-of-experience-10-times that didn’t do this.

The actual code doesn’t matter yet, since I realized this isn’t getting failed logins. I asked another question and got a response. A polite Claude complements me and then rewrites code. This took another few minutes, and it was neat to see it rewriting its code on the right, adding in a new field in the table and adjusting the session.

I watched a bit, but got distracted with a Slack message. One nice thing is I can move on to another task while my assistant keeps working.

At the bottom, I liked the summary of how it works.

My Slack message was from an AE, asking a question about the server property stuff (from the customer). I could have typed a bunch, but when I looked back, Claude was finished, so I asked it.

I copy/pasted this to the AE, as it’s a good summary for the customer. This assistant is making my job easier.

Testing the Solution

I didn’t just send this. Instead I decided to test this on a few local systems. I keep a DBA database on each instance, so I ran the code in there to create the table and session. As a precaution, since this isn’t my code, I ran each item separately.

The table worked here. I had another window for the session, which I looked over, but didn’t extensively check. I’m not an XE expert, and I’d likely fumble this code worse than an AI at first, so I checked the events and actions. I decided to just run this since it looked good.

When it came to the procedure, I got an error. I copied and pasted this into Claude. It recognized an issue and fixed it. This took a few minutes, but this is faster than I could have corrected my own amateurly written query against XE.

Now I had a procedure. One thing I edited in both the session and proc is that I removed the hard coded c:\SQLData path. I wanted this captured with my other instance stuff in the \logs folder, so I left just the name of the XE session.

Adding Archival

One of the things that Redgate Monitor does really well is manage older data. I’ve seen so many people, including myself, set up something like this and then a year later realize they’ve captured GB of data.

I asked Claude to just fix this for me.

I grabbed the second command for my Agent Job and changed 180 to 90.

SQL Agent Job

Claude again asked me above about jobs, but jobs are simple and easy, and I wanted to think about it for a minute. I right clicked and created a new job. I thought about the name and description I wanted. Then I made two steps, pasting in two exec proc commands for the procs my assistant had written.

The last thing was some testing. I ran my job to be sure it was working. It completed, which was good.

I made a few logins and logouts, including a few failed logins. Then I queried my table. I didn’t remember the name, but my assistant tends to pick plain/boring names. So I used SQL Prompt to find it with a ssf <tab> l and got this:

When I got a query and checked, I see logs of my logins and logouts.

Redgate Monitor Custom Metric

My AE and customer wanted to see this in Redgate Monitor, so I decided to ask Claude. It was happy to help.

I could repeat that for the other items (failed logins and logouts). I didn’t, but this gets me ready to add this to Redgate Monitor.

Summary

This shows how an AI assistant can help me set up some auditing for security purposes. There is nothing complex here, and I’ve set up a bunch of this myself in the past. I even have a blog on this.

However, the code is cumbersome and slow to write for me. Or most humans unless you end up working with XE every day. Even if you use SSMS and the GUI to generate the script, it can be slow. I know I’d certainly have to look things up. In less than 15 minutes, I had a fairly well working solution, with archiving (deleting) old data and I didn’t need to focus tightly the entire time or type a lot. I did some other work, and I could focus on just testing.

Claude was a great AI assistant to this problem, which is similar to a lot of DBA-type work I’ve done in the past.

These were the tools I used:
- Claude
- SQL Prompt
- Redgate Monitor
And, of course, Management Studio 22.

Addendum

I tried Google Gemini and ChatGPT. A quick summary of those, which didn’t work as well. At least not to me.

Note that I use the free versions for all of these tools right now.

Gemini

The first prompt got me just a table and trigger, with a followup if I wanted more. I asked about XE and got a basic session, not as easy to read as Claude and embedded inside the response. It also had fewer actions.

I got to the same place, but it was more prompts and I had to keep guiding it along, like micro, or at least mini, managing another DBA. On the plus side, this was faster.

ChatGPT

ChatGPT suggested a trigger, but noted this wasn’t great. It did suggest extended events, and a complete solution. I could have just entered “yes”, but I didn’t. I’m still working on muscle memory at times.

I got each part of the solution separate, but this has me scrolling through explanation and code. Again, I prefer the Claude side-by-side approach, but this works. And it’s fast.

This also kept leading me along the process, which I liked. It certainly likes the checkboxes and Xs in its results.

Both tools helped me with custom metrics.

Video Walkthrough

Here is a a retry, and then showing the first solution. I think Claude learned a bit the second time.
December 17, 2025
Capturing Data Changes

While working with a customer recently, I saw some code from them that used the OUTPUT and INTO clauses of an UPDATE statement to capture the changes made into another table. In this case, as users updated code strings in a table used in dynamic SQL, the developers wanted to capture the history of those changes in another table, giving them a rollback strategy if there were problems. This is an interesting, and focused way of auditing data changes, albeit with the work to write this code and control updates through stored procedures.

This is one way of tracking the history of data values in many database platforms. Temporal tables are another, and we have CDC/Change Capture available in SQL Server. In the past, many people have used triggers. All of these work, with various pros and cons. In many cases, I have seen triggers used, often because many developers know how to write them and they are easy to create. Easy to get wrong as well.

However, triggers take some work, while platforms have often built capabilities that make it easy to capture data changes and track them. Many developers often aren’t aware of these features, or haven’t spent sufficient time with them to know how to work with them or if these features even work well. This is one reason many of us write about new features, to learn about them, experiment, and help others to understand how to use them. Of course, not all features turn out to be as great as marketed.

Today I wonder how you capture changes and audit them. Do you use triggers? Something built in? For limited auditing, and with control of the code, would you use the OUTPUT clause with your insert/update/delete code? Actually, I wonder how many of you would even consider this for limited auditing, especially with the large number of tools and frameworks that might generate their own UPDATE statement rather than call a stored procedure where you control the code. Or do you think this isn’t a good way to capture this data.

Steve Jones

March 27, 2023
Checking Up on Vendors

Many years ago I was in the Denver airport, waiting to board a plane. At the time I was consulting with a small startup company and they called me in a panic, having had an issue with their database deployment and needing to restore the previous version. However, they were getting the “The media set has 2 media families but only 1 are provided. All members must be provided.” error. Fortunately, I knew what was wrong and verbally walked them through the process. When I landed, I logged in and verified things were working and that they’d followed my instructions.

A few years later I was shopping for furniture with my wife one Saturday night and I got a call from a fellow Operations person at JD Edwards. They had some security issue with a SQL Server, and since I was the SME (subject matter expert), they called me for help. Sitting in a comfy lounge chair (which I never purchased), I helped them solve the issue. Later, I verified things were working.

In these cases, I was using remote hands from trusted individuals at an organization. However, these days, many people call cloud vendors (or other hosting companies) for the same type of remote hands support, but without necessarily having the accountability from that staff. Sure you can check if they solved an issue, but are you sure they didn’t read some data or change anything else?

It’s a concern, but it’s one that I think can be solved with help from vendors. Microsoft recently announced there’s an audit capability for Azure SQL Servers, allowing customer to determine what actions Microsoft support engineers might take on your system. Since we may not necessarily see what is happening when someone else is working on our PaaS or IaaS systems, it’s good to have something tracking their moves. I don’t know this gathers all queries run, but it certainly should.

I’ve seen some presentations on how customers have to authorize engineers in VMs with Customer Lockbox, and the technical details are interesting. These are time limited and audited access controls to ensure that customers are aware when a Microsoft employee touches their systems. I don’t know if this is comprehensive enough, but it is a good start.

Over time, more and more of us have to delegate some work to others, either inside or outside of our organization. We need strong controls and good tools to ensure that we can determine what happened, especially in the event of some incident taking place. Building these controls is important, and really, they should be easily enabled in all our systems. While I don’t know we have a lot of rogue employees accessing things, we do have some, and we have more that make mistakes. Auditing their actions should be something we can easily do.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

May 10, 2021
Internal Controls

I was browsing the Internet and stumbled on a small part of a larger story that struck me. Many of you may have heard of the story of Jamal Khashoggi, the journalist for the Washington Post that was killed. I hadn’t spent much time reading about the story, and I don’t really want to discuss that topic here. The politics of the situation are not relevent here.

There’s a part of the NY Times background story that caught my eye when a quote was posted on Twitter. This is part of that quote: ” The intelligence officials told the Twitter executives that Mr. Alzabarah had grown closer to Saudi intelligence operatives, who eventually persuaded him to peer into several user accounts”. Essentially, an employee at Twitter was accused of accessing, and potentially disclosing, sensitive data about customers. This is what I want to discuss.

In my career, there are quite a few times that I’ve had to access data to solve some problem, debug an application, or produce a report. In many cases, I’ve had to maintain some confidentiality of the data, not even discussing specifics with other employees that were not supposed to view that information. To me, that’s just part of being a professional. We handle all sorts of data, some of which we should never use outside of solving an issue or producing a report.

As I thought about what was alleged here, I wonder how many social media companies have controls or auditing to determine who has accessed information. Would they be able to actually produce a report that validates some assertion that data was, or was not, accessed. I doubt many companies have these kinds of controls. Unless some Excel file or other export was on a file share, would there be evidence?

Then I thought does anyone really do a good job of producing audit records for information access? I know some government and law enforcement systems do this (and some legal software), actually tying queries and results to some individual and even a piece of work. That’s not the nature of information for most of us, though perhaps it ought to be.

Auditing data, especially for information access, could be a huge amount of data. Even keeping a record of all user access for a week in most SQL Server databases might be more data than many of us have in our database. I do think we ought to have the option, and I’d hope that we get more detailed, more capable, and more configurable methods of auditing SQL Server activity in the future (Hint, give us SQL Audit data in a csv).

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

November 12, 2018