stpSecurity_Checklist – Best practices and security checklist for SQL Server

Hello guys!
This is a special post for me, because it’s my first post in English here in my blog. The main goal of this post is to share and show how to use the stpSecurity_Checklist Stored Procedure to perform a complete security checklist on your SQL Server instances.

After a big change in my original SP (SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados) to allow multilanguage and spent many hours translating every string to English, I’m finally able to share with you this version of the security checklist.

I started this project in November 2018 and now with more than 6,000 lines of code, this Security Checklist (probably, the most complete you will find on the Internet), with more than 70 security items to check your database, going through the part of settings and parameters, permissions, programming objects and more!

After seeing a lot of companies, developers (and sometimes DBAs themselves) overlook the security of their SQL Server instances, where we see environments which the application uses the “sa” user, for example, or find thousands of attempts to connect with bad password and nobody does anything, instances without a well-defined backup routine, we have decided to create a very practical and easy way to quickly have an overview of SQL Server security, in a friendly format with technical information at the same time, allowing you to easily export to an Excel and demonstrate to the customer the many issues founded, the impact that this can cause on the instance and how to solve.

The output of the stored procedure is organized as follows:

• Code: A unique number to easily identity the validation item
• Category: A way to group the checks according to a logical category I envisioned for these validations
• Title: Verification title, which is a summary of what this item is validating in the database
• Result: Is the result of validation. It informs if the item passed the validation (OK), if it is only an informative item or if it has identified a POSSIBLE problem
• How this can be an Issue: A brief explanation of why this item is being scanned and what security risk it can bring us
• Technical explanation: More technical and specific details of what is being checked on the instance
• How to Fix: Some guidelines on how to correct or circumvent the possible problem identified by the Stored Procedure
• Result Details: XML that returns the records that caused the validation failure and the artifacts identified (some items are limited to TOP (N) records because they can have many records returned in XML)
• External Reference: Link to a article or documentation that might assist in understanding this verification item

If your excuse for not treating the security problems in your company was not having a practical and easy way to identify the issues, didn’t know how to solve or didn’t know what the security problems were, your excuses are gone! This shouldn’t be a problem for you anymore.

This is a project that I ran in several clients here at Fabrício Lima – Soluções em BD , one of the best BI and SQL Server consulting companies in Brazil, and this is the result of a lot of study, tests and technical discussions with several great professionals of the data platform area and after talking with Fabrício, we decided to release it in a FREE way for the entire technical community.

After using sp_Blitz from Brent Ozar, which I think that is incredible, practical and simple to find a lot of items to check performance, maintenance, auditing, and some security features. Thinking of something as practical as this, I was inspired by this idea to develop stpSecurity_Checklist, trying to deliver something as practical as a “F5” to you.

This is not a Dirceu’s or Fabricio’s project, but yours. For this reason, I am releasing the code from this Stored Procedure in Github, so you all can download, use in your environments and help make it better through commit’s and pull requests to bring new features and fixes:
– https://github.com/dirceuresende/checklist_seguranca (download it HERE)

I hope you like this Procedure, a big hug for you and see you next time!