In this blog post, let’s explore one of the fantastic features added to our good old friend SSMS by Microsoft, Security Vulnerability tool. I am currently running SSMS 17.6, but this feature which we are going to see has been introduced in version 17.4. Okay….So, let’s see what this is all about.
I have a database named “ChickenNuggets” which I would like to know what are the potential security risks it has. Well, All I need to do is connect to SQL Instance from SSMS 17.4 or above and Right click on the database and go to tasks and navigate to VA and do a scan. See below screenshot for what I am talking about.
Give it a path and Wait for few seconds and voila…..you get something like this.
As you can see, it reported 10 failed checks for this DB. Also, You can select any item and approve as baseline if that’s normal in your environment, That way SSMS doesn’t include that particular item under failed list as it has been included in baseline.
As you can see it’s giving a detailed explanation along with what exact Query it ran for this particular check and the best part is it gives you remediation script as well.
Now, What If I want to scan something at Instance level, I could perform the same on Master Database.
That’s all good so far right? But there is still huge room for improvement. What if my instance has 100 databases? I hope you already guessed where I am going with this….Yes, That’s correct, I have to repeat this 100 times at individual database level. Well that sucks. Also, I don’t see anyway to export these results to different formats. It would be really nice if Microsoft gave us an option to export this to PDF format or HTML. But hey…this is a start and I am positive Microsoft is listening to community and these will be made available to us very soon in future releases of SSMS.
BTW only SQL Server 2012 Instances and later are supported, and of course Azure SQL Database 🙂 .This is extremely light weight tool which you can run at any time without impacting your workload. It just runs bunch of DMVs…Shouldn’t block anything. But hey, don’t trust anyone and anything posted online when it comes to your Production Databases. After all that’s our Bread and Butter!
Happy weekend folks.Cheers!
MSSQLTREK 