SQL Server security monitoring is a critical part of the Database Administrator’s job. Some security related alerts could be setup very easy, but others require third-party tools or extra steps to setup. For example, SQL Server Audit can be used to monitor logins or users modification, but it requires audit log review. We would like to get real-time alerts every time a login or a user is created or added to a server or a database role, how can this be done?
In this tip we will provide steps and scripts for setting up WMI alerts and jobs responding to these alerts to monitor the creation and removal of users and logins as well as server and database roles membership changes.
2018 started out with bad news for most IT professionals. A new “speculative execution side-channel attacks” vulnerability affected many modern processors and operating systems. This vulnerability is very complex and requires patching on many layers, including hardware, operating systems and the application layer.
Database Administrators (DBAs) have to be prepared to patch every SQL Server starting with SQL Server version 2008 and the recommended patching procedure is different for different servers depending on SQL Server configuration settings and features used.
How can we determine what servers are affected and what steps should we take to protect SQL Server?
In this tip we will provide a “Meltdown/Spectre SQL Server Assessment Script” that you can run in Central Management Server (CMS) against all of your SQL Servers. This script will produce a report with recommendations/scenarios for patching and display configurations/features that are not secure and enabled on SQL Server.
Then we will provide a couple of remediation steps to review unsecure use cases.
After remediation we will run the Main Assessment Script again and review the final recommendations.
Email alerting is an important feature that helps to automate a lot of Database Administration tasks. How can we be sure that Database Mail works properly? If there is email problem there is no another email that will notify us of an issue and it could be days before we realize that there is a problem.
In this tip we will provide a solution to check all of your servers for the different types of Database Mail issues. We are going to use Policy-Based Management (PBM) to perform these checks.
When a SQL Server synonym is created with T-SQL the base object’s existence is checked only at run time. SQL Server Management Studio (SSMS) has some built-in checks that are performed during synonym creation whereas T-SQL scripts can be written several different ways and do not have any checks by default. In this tip I explain what is the best way to create synonyms and why.