Are your SQL Servers secure? With new threats to your data emerging daily, it’s a difficult question to answer. And what is even included in the word “secure”? Sure, maybe you’re confident you have security against hacking (or maybe not?), but what about internal security? Are you protected against oops moments? Are there elevated permissions assigned that you didn’t know about? Are there troublesome configurations with your SQL Server instances and databases?

Do you know what to look for, or even where to start when it comes to SQL Server security?

Here at Straight Path Solutions, we’re big fans of community tools like sp_WhoIsActive, Brent Ozar’s First Responder’s Kit, and Erik Darling’s suite of helpful stored procedures. As database administrators who are constantly looking at new clients and new servers, we wished there was a tool to quickly give a SQL Server security check – an overview of any potential security issues. We didn’t find one, so we made one: sp_CheckSecurity.

It’s free, it’s easy to use, and you don’t even need to give us your email address. You can download it today from the Straight Path GitHub repository.

What does sp_CheckSecurity do?

Maybe you have some scripts you found on the internet to check some SQL Server security settings or look for odd permissions. Or maybe you don’t. Well, sp_CheckSecurity checks about 40 different objects, configurations, and permissions for possible issues. You can read more details about it on the sp_CheckSecurity page.

After completing all these checks, you will get a single result set with any issues found, ordered by vulnerability level. Each row will identify:

• What vulnerability we found
• Why it is a (potential) issue
• How big of a vulnerability the issue is
• What our recommendation for resolving the issue is
• A link for more information about the issue.

How do I use the tool to check SQL Server security?

Execute the script to create sp_CheckSecurity in the database of your choice, although we would recommend the master so you can call it from the context of any database.

Although you can simply execute it as is, there are currently three parameters.

@help – the default is 0, but setting this to 1 will return some helpful information about sp_CheckSecurity and its usage in case you aren’t able to read this web page.

@ShowHighOnly – the default is 0, which returns all findings. If you only want to focus on the most important findings, you can reduce the result set by setting this to 1.

@CheckLocalAdmin – the default is 0, which does not check the members of the local Windows Administrators group.

Again, there is more about these parameters on the main sp_CheckSecurity page. Please read that page before executing the stored procedure. We also list information about each of the 30+ SQL Server security checks the tool performs there with some details about why the checks are security findings and provide some links for resolving those security concerns in your instances and databses.

What are the requirements to use sp_CheckSecurity?

There are two requirements.

1. You need to be in the sysadmin role. This tool is designed to be used by administrators only, as they are the only ones who can address many of the vulnerabilities and discrepancies that could be found. If you aren’t in the sysadmin role, this isn’t the stored procedure you’re looking for.
2. Your SQL Server instance needs to be using SQL Server 2012 or higher. If you are using an earlier version, execution of the stored procedure will be aborted because some of the DMVs used don’t exist in earlier versions.

What if I have any questions or feature requests?

Please report any issues on our GitHub sp_CheckSecurity Issues page.

Final thoughts

This is by no means a comprehensive security check for your SQL Server or production environment. In fact, we offer our clients a more thorough security review where we look at even more configurations and permissions on your SQL Server instances. But we hope sp_CheckSecurity can help SQL Server Database Administrators like you to identify and address any obvious or potential security vulnerabilities.