Nisarg Upadhyay
Restrict Access must be Single_User

Send an alert when the database state changes to SQL Server single-user mode

December 18, 2019 by

This article explains the SQL Server single-user mode and how to configure an automated email alert when the state of the user database changes to the single-user mode. To send an alert, we will create a DDL trigger that executes when the user runs the ALTER DATABASE query. It collects the pieces of information using EVENTDATA() function, saves it in a temporary table, and sends an HTML formatted email.

First, let me give you the summary of DDL triggers, EVENTDATA() function, and SQL Server single-user mode.

What are DDL triggers

SQL Server DDL trigger is a special procedure that is executed when any server-scoped or database-scoped event occurs. For example, a DDL trigger could be executed if we create a new database using the CREATE DATABASE command or alter the configuration of the database using the ALTER DATABASE command. It can be executed when any database scope event occurs, for example, if we create a table using the CREATE TABLE command, change the table structure using the ALTER TABLE command, or truncate the data from the table using TRUNCATE TABLE command. The changes are recorded in an XML format, which can be accessed using the EVENTDATA() function.

You can view the server scoped database in SQL Server Management Studio’s Object Explorer in the Triggers folder. To view it, navigate to SQL Server Database Engine | Server Objects | Triggers. See the following image:

Server scoped trigger in SQL Server management studio.

You can also view database scoped triggers, just navigate to SQL Server Database Engine | database_name | Programmability | Database Triggers. See the following image:

Database trigger in SQL Server management studio.

Alternatively, you can view the database trigger by executing the following query:

Following is the output:

Database trigger

You can view the server scoped triggers by executing the following query:

Following is the output:

server scoped trigger

You can read Database Level DDL Triggers for Views, Procedures and Functions article to understand the concept of DDL triggers in detail.

What is EVENTDATA() function

As I mentioned, we are going to use the EVENTDATA() function in the DDL trigger to populate the data. It captures the XML-formatted data about the event that occurred in the DDL trigger. Following are the keynotes about the EVENTDATA() function:

  1. EventData returns data when directly referenced directly inside the DDL trigger
  2. The definition of the all events are in: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd
  3. The data becomes invalid after the transaction that called the EVENTDATA()
  4. The EventData schema returns the following details:
    1. Type of event
    2. The timestamp of the event
    3. The SPID of the connection that executed the trigger
    4. Server Name
    5. Login Name
    6. T-SQL Query/command

SQL Server single-user mode

When the database is in the single-user mode, only one user can connect to the SQL Server instance. When you set the database to single-user mode, the following will happen. All the other connections will be closed automatically without a warning. When the database is in the single-user mode and if any user or application tries to connect the database, it gets the following error:

Msg 924, Level 14, State 1, Line 1
Database ‘DBA’ is already open and can only have one user at a time.

To put any user database in SQL Server single-user mode, you can use any of the following methods:

Using SQL Server Management Studio

To put a database to SQL Server single-user mode, open SQL Server Management Studio, expand Database Engine, right-click on the database which you want to put it in single-user mode, and select database properties. See the following image:

Right-Click on database properties

In the Database Properties dialog box, click on Options, and from the Restrict Access drop-down box, select SINGLE_USER and click OK. See the following image:

Restrict Access must be Single_User

Using T-SQL query

Alternatively, you can use the following command to put any user database in SQL Server single-user mode:

Alter database <UserDatabaseName> set single_user with <Termination_Option>

Alter database set single_user command can be combined with the following three termination options:

  1. With rollback immediate: When you use rollback immediate option, it rollback all the active incomplete transactions and put the database in SQL Server single-user mode
  2. With rollback AFTER [INT] SECONDS: When you use rollback after option, it will rollback all the active incomplete transactions after the seconds specified in the command
  3. With NO_WAIT: When you use NO_WAIT, it will try to run the ALTER database command. If the command cannot change the option, then the process which is running the alter database command terminates itself

Configure the SQL Server Database Mail

SQL Server Database Mail is used to send the email using the Database Engine. This is handy when we want to send the query result, configure any alert or notification. SQL Server Database Mail uses the SMTP protocol to send emails.

Now, to send the email notification, I am going to use my personal email account. I am using Microsoft Outlook; hence, I will use the SMTP configurations of Microsoft Outlook. I am adding SMTP server details of Gmail Outlook and Microsoft Live/Hotmail.

Mail service providers

SMTP server name

Port

Microsoft Hotmail

smtp.live.com

587

Gmail

smtp.gmail.com

587

Microsoft Outlook

smtp.office365.com

587

To configure the database mail, you should enable the database mail feature in SQL Server. To do that, you must run the following query in sequence:

Following is the output:

Enable database mail using T-SQL Command.

Once database mail is enabled, execute the following scripts to configure Database Mail. We must run these queries in the proper sequence:

To configure Database Mail profile and database mail account, execute the following queries in SQL Server Management Studio:

Script to create database mail profile and account.

You can review Database Mail settings by executing the following query:

Following is the output:

The Database Mail configuration and settings

If you, for some reason, get into trouble while setting up Database Mail – check out the following detailed article on this subject: How to configure database mail in SQL Server

T-SQL script of DDL trigger

The DDL trigger executes when the ALTER DATABASE command issues on the SQL Server database. The trigger collects details of command, processID, time and from EVENTDATA() function and stores it in the temporary table named #TempAudit. See the following code fragment:

To display the T-SQL Command, SQL Query, LoginName, and database name in tabular format, we have created a dynamic SQL query with HTML tags and save the query in @tableHTML variable. See the following code fragment:

Using SQL Server Database Mail, it sends the data to the desired recipients. See the following code fragment:

Following is the entire script of the database trigger:

Test DDL trigger

To test the trigger, we will change the DBA database to SQL Server single-user mode. Following T-SQL query changes the state of DBA database to SQL Server single-user mode:

Once the command executed successfully, the database mode changes to a single-user, and I received an email notification in my mailbox. See the following screenshots:

User Database DBA is in SQL Server single user mode

Following is the screenshot of the email:

Conclusion

In this article, I have explained about the DDL Triggers, SQL Server single-user mode, and EVENTDATA() function. I have also explained the DDL trigger, which can be used to send an email alert when any user changes the mode of the database.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
2,822 Views