In recent weeks I’ve been conducting a bit more of our own internal SQL Server Security Checks than usual for our clients. This shouldn’t be a surprise, what with all the stories about “rouge AI” breaches and with Microsoft releasing A LOT more security fixes in their recent Windows and SQL Server updates.
In all these Security Checks we use our sp_CheckSecurity stored procedure to collect a big chunk of the findings. I wrote the original version of this tool a while back, and out of curiosity I wanted to see how long ago that was. It turns out I created the first working version of the tool four years ago this week. Wow, four years! And the things that I have found using that tool could, as my late stepfather might say, make your eyes water.
Inspired by my colleague Andy Kelly’s post introducing the sp_CheckActivity tool that he’s been working on in various forms for years, I thought I would share a bit about the history and intention of sp_CheckSecurity in a similar manner.
How did sp_CheckSecurity come to be?
According to my notes, in October of 2022 I was meeting with a client discussing some findings from some internal Health Checks we had recently done for their SQL Server instances. And my notes from the conversation indicated that they “definitely want to focus on reviewing security” for those instances.
At the time, Straight Path didn’t have a specific product for reviewing SQL Server security. We had scripts for reviewing individual items, and there were (and still are) some security-related checks in free tools like Brent Ozar’s sp_Blitz, but the client indicated they wanted a deeper review to find vulnerabilities. So, after speaking with Mike Walsh about this, I wrote a script.
I had never even remotely considered myself a SQL Server security expert, so I consulted with my colleagues here at Straight Path, primarily with Mike Walsh and Mike Lynn, and put together a list of about 25 things to check. The checks were for things like logins with elevated permissions, condition of the sa login, and public permissions. I put them all in my T-SQL script that would return three columns: Issue, Database (if applicable), and Description. That’s it.
As embarrassing as it now to have used such a simple script, the client was pleased with the findings since there were quite a few things that were actionable. I took that feedback and kept working to add more possible vulnerabilities to the script.
The interesting thing is that the more I started looking into finding additional vulnerabilities, the more I wanted to keep finding them. Throughout 2023 I sought out blog posts and sessions about SQL Server vulnerabilities and security recommendations, absorbing years’ worth of posts from Andreas Wolter’s blog and being shocked by Erland Sommarskog’s session on permissions hijacking. The more I learned, the more checks I added to my script.
But at the same time, the more fearful I became for folks. I know security can be a less appealing topic, and I have noticed security sessions aren’t always well attended. And when I had the opportunity to speak with folks personally about security, there were only a handful that would discuss the topic in any depth.
That’s not a slight against anyone, but rather a note about how it isn’t always front of mind for database folks. One of the reasons might be that we can measure performance improvements to stored procedures with all kinds of metrics, but it can be difficult to quantify to a supervisor what we’ve done to protect the data. I mean, if you tell your supervisor you removed three orphaned users with elevated permissions and changed a group from db_owner to only have read, write, and execute permissions…you might get a nod, if you’re lucky. More likely you’ll just get a blank stare.
Why did we release a public versions?
In 2023 I added more checks, made a supplemental PowerShell script to collect more info for the Security Check reports, and completed these reports for mostly new clients. And what I saw made me really…concerned.
- There was the client that had over 90 members of the sysadmin group. On several instances.
- Another had DOMAIN\Users in the sysadmin group.
- Too many clients that had logins with a password that was the same as the login. Or the word “password”. And plenty of those logins were also in the sysadmin role.
- I’ve found SQL Server service accounts in DOMAIN\Administrators.
- I’ve found the public role with unexpected permissions like BACKUP DATABASE and ALTER ANY DATABASE.
- And I’ve found way too many linked servers using the sa login or some other member of the sysadmin role.
Every new client seemed to have at least one instance that had some sort of “What the Fudge?!?” finding that left me…deeply concerned. So I spoke with Mike Walsh and our COO Evan Corbett in early 2024 about releasing a version of my script as a publicly available stored procedure, with the intention of helping other folks who may have inherited SQL Server instances to find and review any WTF-type findings in their own environment. Since there was no similar publicly-available tool, they both agreed this would be the right thing to do.
By that point the script had evolved to have more columns, but I changed the stored procedure version to mimic my favorite thing about sp_Blitz: a column with a URL so people can read more about any given finding. Of course, this meant I needed to create the content for those URLs and pages, and after a few weeks of work I had those on our website for nearly all the 40 or so issues we were checking.
We arrived at the name “sp_CheckSecurity” since we had a phrase on our website that said “We sell green checkmarks!” We wanted to emphasize the “Check” as a sort of brand on which we could build more tools. As I recall, in that same conversation I volunteered that after releasing sp_CheckSecurity I would start working on sp_CheckBackup, since backups are crucial to recovery after any security-related event.
In April of 2024 sp_CheckSecurity was released, and around the same time I created a “Defending SQL Server” session to present the most frequent findings I had seen when conducting Security Checks for clients. I have modified this session over the years to match frequent findings and newer checks, and have continued to present the session as recently as last month.
What issues do I keep seeing?
If you haven’t seen that session (you can find a version from 2025 on YouTube), I can tell you that in my hour presentation I try to cover the most dangerous and easily resolvable findings I have seen over the last 4 years. Even though there are close to 100 checks in sp_CheckSecurity now, the biggest issues I keep seeing are almost always the same.
- The sa login is enabled. This is especially troubling when I create a SQL Audit or Extended Event and find that the login is not only enabled, but also used by applications or even individual users. (Note: I know Andreas recommends leaving the sa login enabled with a humongous password and using it as a honeypot, but that plan only works if someone is monitoring the usage of the account.)
- The logins that have CONTROL SERVER permissions. These are almost always from some software vendor, whom I can only presume didn’t want to raise concerns by adding the login used by their application to the sysadmin role…so they did the next best thing.
- The numerous members of the sysadmin role. Someone has to be in the role, but I keep seeing 10 or 20 or more members of this group on so many instances that I think some database admins are just lazy.
- The numerous members of the db_owner role. Speaking of permissions from lazy admins, this is probably the most common. Folks must not realize that members of this group can easily escalate permissions to the owner of the database – and that’s usually a member of the sysadmin role. Seriously, scroll back up and watch that session from Erland that I linked and you’ll be…deeply concerned about this and other database roles.
- Databases with TRUSTWORTHY enabled. If folks knew what this does, you would never actually enable this setting. It’s often the golden ticket of privilege escalation.
- The SQL Server service accounts in the sysadmin role or the local Windows Administrator role. 99% of the time the service accounts will get all the permissions they need by adding or changing them in SQL Configuration Manager. If folks used that instead of the Services control panel then they wouldn’t need to grant these other excessive privileges.
- The SQL Server service accounts using NT AUTHORITY\SYSTEM. I’ve heard and read lots of folks express concern about whether xp_cmdshell is enabled. Here’s the thing: anyone in the sysadmin role can enable xp_cmdshell at any time, so you’re better off worrying about (1) who has permissions to use xp_cmdshell and (2) limiting the permissions to what can be executed with xp_cmdshell. And using NT AUTHORITY\SYSTEM allows way more permissions than your SQL Server services need.
- The SQL Server instances that have never gotten updates. In the last two months Microsoft’s updates for SQL Server have had something like 60-80 security fixes, depending on which version you are running. It’s 2026 and too many folks still can’t figure out how to schedule and apply updates on a monthly or even quarterly cadence.
- Linked Servers using fixed logins in the sysadmin role. Let’s just say that I have learned to despise linked servers with the fire of a thousand suns.
- Failure to review failed logins. This goes back to what I said previously about disabling the sa login. Folks aren’t even noticing the hundreds or thousands of failed logins already captured in their SQL Server Error Log, which hopefully are from misconfigured credentials and not a brute force attack.
What’s next?
I’ve enjoyed getting feedback on sp_CheckSecurity over these last few years, and I hope it’s helping database professionals correct vulnerabilities and protect their organization from threats. I’m still reading blog posts from Andreas and Fabiano Amorim and Vlad Drumea and others with great interest, and looking for any sessions from them or Danny de Haan or Thomas Grohser or some other security-minded folks. And of course my colleagues, a group that now includes our Chief Data Officer Buck Woody, keep me on my toes by asking me “Does sp_CheckSecurity check for (something it doesn’t yet check for)?”
Even though I’ve written five sp_Check tools, sp_CheckSecurity was the first. (Well, actually I wrote a script that became the core of sp_CheckTempdb years earlier, but nevermind that.) And now that I’ve been updating and using it for four years, I’ve got a huge fondness for it. The other tools are useful and can help folks discover some nasty problems with their SQL Server instances, but I’d like to think sp_CheckSecurity can not only help you find and correct vulnerabilities, but that in doing so it can help you to protect your entire organization.