MSSQLTips: Tip #51 – Alerts and Notifications for SQL Server Login, Database User and Role Membership Changes

Featured

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.

Please read the latest MSSQLTips post: “Alerts and Notifications for SQL Server Login, Database User and Role Membership Changes“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #50 – Different ways to sort multi-server SQL Server query results using Central Management Server

Featured

Central Management Server (CMS) is a SQL Server feature that allows you to manage multiple SQL Servers at the same time. You can run T-SQL scripts or execute Policy-Based Management policies on multiple servers. You can also group SQL Servers and run the scripts on a specific group of SQL Servers.

How else can CMS be used? How can we overcome some of the limitations when we run queries against CMS Server Groups?

In this tip we will show you a couple of CMS tricks. In our examples below we will show how you can sort the CMS query results for different scenarios. We will also provide a couple of examples of alternative uses of CMS.

Please read the latest MSSQLTips post: “Different ways to sort multi-server SQL Server query results using Central Management Server“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #49 – SQL Servers Assessment for the Meltdown and Spectre Vulnerabilities

Featured

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.

Please read the latest MSSQLTips post: “SQL Servers Assessment for the Meltdown and Spectre Vulnerabilities“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #48 – SQL Server Database Mail Health Check with Policy Based Management

Featured

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.

Please read the latest MSSQLTips post: “SQL Server Database Mail Health Check with Policy Based Management“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #47 – SQL Server Database Mail Health Check for all SQL Servers

Featured

Most of the SQL Server DBAs will agree that we rely on email alerts too much. Tasks are automated, we have emails notifying us of failed jobs, of errors, etc. But how can we be sure that emails are always sent? If there is an email problem there is not 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 monitor all of your servers for the different types of Database Mail issues. We will provide a T-SQL script that is executed with a PowerShell script. The PowerShell script can be scheduled to run on a Central Management Server (CMS) or another SQL Server. In our example we will run it once a day to check the Database Mail health, but you can modify it and run it every hour or as often as you need.

Please read the latest MSSQLTips post: “SQL Server Database Mail Health Check for all SQL Servers“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #46 – Safely Dropping Unused SQL Server Indexes

Featured

There are many resources online about finding and dropping unused SQL Server indexes, but there a number of issues related to unused indexes removal. How can we make sure that we are dropping unused SQL Server indexes safely? What are the potential issues?

Some DBAs probably have an unused indexes review as part of their routine. Experienced DBAs understand the consequences of index removal and the potential issues. In this tip, we will review some of these potential issues and we will provide you with a couple of examples when you should just leave the indexes as is (even if they are unused).

Please read the latest MSSQLTips post: “Safely Dropping Unused SQL Server Indexes“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #45 – Drop Login issues for logins tied to SQL Server Availability Groups

Featured

During our regular security reviews we have discovered that one of the SQL Servers with Availability Groups has an individual DBA’s login in addition to the DBA group as a member of the sysadmin server role. The DBA is already a member of the DBA Domain group, so we are not sure why we had this login on SQL Server.

We have tried to remove the login, but couldn’t remove it.

In this tip we will be providing troubleshooting steps required for the Drop Login issues.

Please read the latest MSSQLTips post: “Drop Login issues for logins tied to SQL Server Availability Groups“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQLTips: Tip #44 – SQL Server Master Data Services Business Rules Notifications

Featured

You can validate Business Rules manually using MDS Explorer, Version Validation or the MDS Excel add-in. You can also automate validation of the Business Rules by using the mdm.udpValidateModel stored procedure. In this case we don’t have information about the validation results right away (as the validation process might run at night), but we can setup email notifications to review the Business Rules validation status later or without using the MDS Administrator site.

In this tip we will be providing steps required for the notifications setup.

Please read the latest MSSQLTips post: “SQL Server Master Data Services Business Rules Notifications“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #36 – Create Business Rules in SQL Server Master Data Services

Featured

In this tip we have provided the MDS Business Rules overview. You can also find out how to create a simple rule.

Please read the latest MSSQL Tip: “Create Business Rules in SQL Server Master Data Services“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #35 – More Master Data Services (MDS) Tips and Tricks

Featured

Here are more tricks and tips that will help you to get more familiar with SQL Server Master Data Services (MDS), it’s functionality and interfaces.

Please read the latest MSSQL Tip: “More Master Data Services (MDS) Tips and Tricks“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share