Using SentryOne to Alert on SQL Server Database Object Changes

In my previous blog post, “SentryOne Advisory Conditions on GitHub,” I introduced the SentryOne repositories available on GitHub. Recently, I added several new conditions for auditing changes related to stored procedures, tables, and triggers. In this blog post, I’m going to explain how those conditions work and how you can modify them to best suit your alerting requirements. I’ll also show you how to create custom and detailed email alerts through the sample Execute SQL actions. See the documentation on SentryOne Docs for additional information on accessing and downloading Advisory Conditions from GitHub.

The Auditing Conditions

Every now and again, someone asks if SentryOne can be used to alert on changes to objects such as stored procedures or tables to avoid unpleasant surprises after issues from the changes appear. Not too long ago, I was connected with someone who was surprised to learn that a colleague had re-added some triggers to a database after they had been removed for causing performance issues. In most cases, if the information exists and is accessible on your monitored targets, then Advisory Conditions can be created to check for the scenario, and Actions can be used to alert or respond to them.

One of the only caveats is that you don’t want to create conditions that add significant overhead to your environment. If you add excessive auditing to your servers or check for the conditions too frequently, that can happen. Just keep that in mind before using these samples to create additional audits on all the object types and changing settings to get the results every 30 seconds. 😉

The new auditing conditions that I’ll be discussing are:

• Stored Procedures – Created
• Stored Procedures – Modified
• Tables – Created
• Tables – Modified
• Triggers – Created

All of them follow the same condition setup, query, and results pattern, as described below.

Advisory Condition Setup

These conditions are SQL Server queries that are set by default to run once every hour (Default Evaluation Frequency = 1 Hour), with a Severity level of Medium.

The SQL Server Query is set to match the Default Evaluation Frequency. When this condition executes, it looks for changes that have taken place within the past hour.

WHERE [p].[modify_date] >= (SELECT DATEADD(HH, -1, GETDATE()))

If you change the Default Evaluation Frequency to 15 minutes, then you would need to change the HH to MI and the -1 to -15, as shown in the following example:

WHERE [p].[modify_date] >= (SELECT DATEADD(MI, -15, GETDATE()))

See the Microsoft Docs article on DATEADD for details and examples.

The query is also set to ignore system databases (database_id > 4) and databases that aren’t online (state = 0).

WHERE database_id > 4 AND state = 0;

You might be interested in including system databases or even making a separate condition that is for system databases only. I think you’ll want to exclude tempdb in most cases, though, especially for the Tables – Created condition. 😅

See the Microsoft Docs article on sys.databases for more information about the database_id and state columns.

Advisory Condition Results

The SQL Server Query queries all the databases in sys.databases and their associated database objects (e.g., sys.procedures, sys.tables, or sys.triggers). If the condition evaluates to True, then the results are returned as a list of values and keys. The Key is the name of the database with objects meeting the condition criteria, and the Value is the number of objects involved.

For example, if you receive the following results from the Stored Procedures – Created condition, it means that the AdventureWorksDW2017 database has 11 stored procedures that have been created, and the AdventureWorksLT2017 database has 2 stored procedures that have been created.

The results will include a list of up to 100 databases. The maximum number of results allowed in the set corresponds to the Maximum Instance Count. For more information on this setting, see my “Advisory Conditions: Maximum Instance Count” blog post.

Action: Send Email

If you’ve set up an email action, you would see something like the following, where the name of the target is in the subject, followed by the name of the condition, and the body starts with:

AND *TRUE*
SQL Server Query [(SQLSentry), 1] > [0] *TRUE*

SQLSentry is the name of the database and 1 is the number of tables modified.

At this point, you can manually run the query in the description of the associated condition to get a detailed list of results from each of the databases listed. For example, the Stored Procedures – Modified condition contains the following query in the description:

SELECT schema_name(schema_id), [name], modify_date
FROM sys.procedures
WHERE modify_date >= (SELECT CONVERT(DATE,GETDATE()))
AND modify_date != create_date
ORDER BY modify_date DESC;

This query selects all the stored procedures for the database you execute it against that have been modified today and orders the results by the most recent modifications. This query doesn’t just use the last hour because you might not run these queries as soon as you get the alert. You can adjust this query as needed, of course.

This method will alert you that there’s something to look at and tell you where/how to find the details, but it’s a bit more work than using an Execute SQL action and having a detailed list emailed to you instead.

Action: Execute SQL

Would you rather receive an email that lists the server, database, schema, object name, and create/modify date instead? Adding an Execute SQL action to the condition allows you to receive an email similar to the one shown below:

I’m going to walk through the basics that pertain to these conditions. Please see the “Customizing Your Alert Emails in SentryOne” blog post from Lori Edwards for a complete walk-through of how to use the Execute SQL action to generate and format an email like the one in this example.

I added the following files to the neveropen-samples repository under condition-actions:

• Execute SQL Action for ‘Stored Procedures – Created’ Advisory Condition.sql
• Execute SQL Action for ‘Stored Procedures – Modified’ Advisory Condition.sql
• Execute SQL Action for ‘Tables – Created’ Advisory Condition.sql
• Execute SQL Action for ‘Tables – Modified’ Advisory Condition.sql
• Execute SQL Action for ‘Triggers – Created’ Advisory Condition.sql

Using the Sample Actions

These samples are configured to generate a list based on a 1-hour Default Evaluation Frequency. If you change that in the condition, you must also change the DATEADD function in the .sql file (as well as any other changes you make to the database_id, state, or other filters you might add) to keep the results in sync. If you’re worried that you might miss a change because this action kicks off just after the condition evaluation, you could always do something like change the DATEADD to use 61 minutes instead of 1 hour.

In order to use these samples, you must:

1. Ensure that your SMTP settings are configured in SentryOne.
2. Ensure that Ad hoc distributed queries is enabled (a requirement for using sp_sentry_dbmail_20, as explained in this documentation article).
3. Update the @recipients parameter in the .sql files to use your email address instead of YourEmail@GoesHere.com. You’ll find this parameter near the bottom of the text.
4. Right click the condition in the Conditions List, then select Add Actions. The Actions Selection: Advisory Conditions window will open.
5. Select the box next to Execute SQL, then select the OK button.
6. Navigate to View -> Conditions. Select the row with the Execute SQL action for the associated condition name. Paste the contents of the .sql file into the T-SQL Command Text box on the Action Settings tab.
7. Your action is configured! Refer to the Actions article on SentryOne Docs and Lori’s “Customizing Your Alert Emails in SentryOne” blog post for additional details on adding and configuring an Execute SQL action if needed.

What’s Next?

These conditions and actions have been provided as samples to get you started. You might want to customize them for specific needs in your monitored environment and use additional filters on databases and object names.

If you don’t already have SQL Sentry, now is a great time to download a trial and see these features in action across your environment.

Melissa Connors

Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.