SQL Server Blog

SQL Server Tool Update: sp_CheckSecurity version 1.1

Since releasing our initial version of sp_CheckSecurity, we have received quite a bit of feedback. Thank you for all the emails, blog comments, and issues noted in GitHub, that have all been helpful in directing us towards making this SQL Server tool more useful.

Today we are announcing the release of version 1.1, available for download at the sp_CheckSecurity GitHub repository, which addresses several issues.

The updates in this release include:

  • Added notation of version and version date
  • Added check for Ad Hoc Distributed Queries
  • Added check for Database Mail XPs
  • Added check for Ole Automation Procedures
  • Added check for number of error log files
  • Added of @PreferredDBOwner parameter
  • Added default for @PreferredDBOwner is “sa” (or whatever it was renamed to) if @PreferredDBOwner is NULL
  • Updated “database owner not sa” check to “database owner not preferred owner”
  • Added of check for IP address
  • Added of check for Database Mail XPs
  • Updated vulnerability level for securityadmin members
  • Updated TRUSTWORTHY database check into two checks based on owner permission level
  • Updated vulnerability level of role members in master databases
  • Updated check for recent for product level including recent vulnerability updates (GDRs)
  • Removed requirement to create sp_CheckSecurity in the master database
  • Corrected some typos here and there

For the newer checks we have added additional content pages on our website, which you can access via links the output. Or, if you have the free time to scroll through 1200+ lines of T-SQL, you can peruse the source code and copy the URLs.

We are already working on further updates for future releases, so please let us know if you have any suggestions to improve sp_CheckSecurity!

Article by Jeff Iannucci
Jeff loves to help others save time. Whether improving query performance or reducing downtime for maintenance tasks or migrations, he’s not only passionate about discovering ways to save our most valuable resource, but also about sharing what he’s learned. Starting as an accidental DBA in 1998, Jeff has worked extensively with SQL Server database design, administration, and T-SQL development. He appreciates any opportunity to share his knowledge with the community, and has been presenting at user groups and writing blog posts since 2018. He was chosen as an IDERA ACE for 2020, and in 2021 became an author for Pluralsight. When not resolving database issues, he enjoys watching Indycar racing, attending Arizona Diamondbacks and Phoenix Suns games, and cooking Italian food for his wife and four children.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This