当数据库状态更改为SQL Server单用户模式时发送警报

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.

本文介绍了SQL Server单用户模式，以及当用户数据库的状态更改为单用户模式时如何配置自动电子邮件警报。 为了发送警报，我们将创建一个DDL触发器，该触发器将在用户运行ALTER DATABASE查询时执行。 它使用EVENTDATA（）函数收集信息片段，将其保存在临时表中，然后发送HTML格式的电子邮件。

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

首先，让我为您提供DDL触发器，EVENTDATA（）函数和SQL Server单用户模式的摘要。

什么是DDL触发器 (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.

SQL Server DDL触发器是一个特殊过程，在发生任何服务器范围或数据库范围的事件时执行。 例如，如果我们使用以下命令创建新数据库，则可以执行DDL触发器 创建数据库 命令 要么 更改数据库的配置 使用 更改数据库 命令。 可以在发生任何数据库作用域事件时执行它，例如，如果我们使用CREATE TABLE命令创建表， 使用ALTER TABLE命令更改表结构，或使用TRUNCATE TABLE命令截断表中的数据。 更改以XML格式记录，可以使用EVENTDATA（）函数进行访问。

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:

您可以在SQL Server Management Studio的“ 对象资源管理器”中的“ 触发器”文件夹中查看服务器范围的数据库。 要查看它，请导航到“ SQL Server数据库引擎” 。 服务器对象 | 触发器 。 见下图：

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

您还可以查看数据库范围的触发器，只需导航到SQL Server数据库引擎 | SQL Server 。 database_name | 可编程性 | 数据库触发器 。 见下图：

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

或者，您可以通过执行以下查询来查看数据库触发器：

SELECT name, 
       object_id, 
       type_desc, 
       create_date
FROM sys.triggers;

Following is the output:

以下是输出：

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

您可以通过执行以下查询来查看服务器范围的触发器：

SELECT name AS [Trigger Name], 
       TriggerEvent.type_desc AS [Event], 
       Triggers.type_desc AS [Trigger type], 
       create_date
FROM sys.server_trigger_events TriggerEvent
     INNER JOIN sys.server_triggers Triggers ON TriggerEvent.object_id = Triggers.object_id;

Following is the output:

以下是输出：

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

您可以阅读有关视图，过程和函数的数据库级DDL触发器文章，以详细了解DDL触发器的概念。

什么是EVENTDATA（）函数 (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:

如前所述，我们将在DDL触发器中使用EVENTDATA（）函数填充数据。 它捕获有关DDL触发器中发生的事件的XML格式的数据。 以下是有关EVENTDATA（）函数的主题演讲：

1. EventData returns data when directly referenced directly inside the DDL trigger
   直接在DDL触发器中直接引用时，EventData返回数据
2. C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd 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()
   调用EVENTDATA（）的事务后，数据变为无效
4. 1. Type of event
      活动类型
   2. The timestamp of the event
      事件的时间戳
   3. The SPID of the connection that executed the trigger
      执行触发器的连接的SPID
   4. Server Name
      服务器名称
   5. Login Name
      登录名
   6. T-SQL Query/command
      T-SQL查询/命令

SQL Server单用户模式 (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:

当数据库处于单用户模式时，只有一个用户可以连接到SQL Server实例。 当您将数据库设置为单用户模式时，将发生以下情况。 所有其他连接将自动关闭，而不会发出警告。 当数据库处于单用户模式下并且如果任何用户或应用程序尝试连接数据库时，它将收到以下错误：

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

消息924，第14层，状态1，第1行
数据库“ DBA”已经打开，一次只能有一个用户。

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

要将任何用户数据库置于SQL Server单用户模式，可以使用以下任何一种方法：

使用SQL Server Management Studio (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:

要将数据库置于SQL Server单用户模式，请打开SQL Server Management Studio，展开“数据库引擎”，右键单击要置于单用户模式的数据库，然后选择数据库属性。 见下图：

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:

在“ 数据库属性”对话框中，单击“ 选项” ，然后从“ 限制访问”下拉框中选择SINGLE_USER ，然后单击“ 确定” 。 见下图：

使用T-SQL查询 (Using T-SQL query)

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

或者，您可以使用以下命令将任何用户数据库置于SQL Server单用户模式：

Alter database <UserDatabaseName> set single_user with <Termination_Option>

更改数据库<UserDatabaseName>并使用<Termination_Option>设置single_user

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

Alter database set single_user命令可以与以下三个终止选项结合使用：

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 立即回滚选项时，它将回滚所有活动的未完成事务，并将数据库置于SQL Server单用户模式下
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 使用回滚AFTER [INT]秒后：使用回滚后选项时，它将在命令中指定的秒数后回滚所有活动的未完成事务
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 使用NO_WAIT：使用NO_WAIT时，它将尝试运行ALTER数据库命令。 如果该命令无法更改该选项，那么运行alter database命令的进程将自行终止

配置SQL Server数据库邮件 (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.

SQL Server数据库邮件用于使用数据库引擎发送电子邮件。 当我们要发送查询结果，配置任何警报或通知时，这非常方便。 SQL Server数据库邮件使用SMTP协议发送电子邮件。

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.

现在，要发送电子邮件通知，我将使用我的个人电子邮件帐户。 我正在使用Microsoft Outlook； 因此，我将使用Microsoft Outlook的SMTP配置。 我正在添加Gmail Outlook和Microsoft Live / Hotmail的SMTP服务器详细信息。

Mail service providers	SMTP server name	Port
Microsoft Hotmail	smtp.live.com	587
Gmail	smtp.gmail.com	587
Microsoft Outlook	smtp.office365.com	587

邮件服务提供商	SMTP服务器名称	港口
Microsoft Hotmail	smtp.live.com	587
邮箱	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:

若要配置数据库邮件，应在SQL Server中启用数据库邮件功能。 为此，必须按顺序运行以下查询：

/*Enable advanced option*/
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
/*Enable database mail*/
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Following is the output:

以下是输出：

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

启用数据库邮件后，执行以下脚本来配置数据库邮件。 我们必须以适当的顺序运行这些查询：

/*Configure SQL Database mail using the SMTP server of Microsoft Outlook*/
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Database_Mail_Account',
@email_address = 'nisargupadhyay87@outlook.com',
@mailserver_name = 'smtp.office365.com',
@port=587,
@enable_ssl=1,
@username='nisargupadhyay87@outlook.com',
@password='<YourPassword>'
 
/*Create database mail profles*/
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Database_Mail_Profile',
@description = 'DB Mail Service for SQL Server' 
 
/*Add database mail account to the profile*/
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Database_Mail_Profile',
@account_name = 'Database_Mail_Account',
@sequence_number =1 ;
 
/*Grant access to the database mail profile*/
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Database_Mail_Profile',
@principal_id = 0,
@is_default = 1

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

若要配置数据库邮件配置文件和数据库邮件帐户，请在SQL Server Management Studio中执行以下查询：

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

您可以通过执行以下查询来查看数据库邮件设置：

SELECT p.NAME        AS ProfileName,
       email_address AS [EmailAddress],
       display_name  AS [Display Name],
       servername    AS [SMTP Server Name],
       port          AS [Prot Number],
       enable_ssl    AS [Is SSL enabled?]
FROM   msdb.dbo.sysmail_profile p
       JOIN msdb.dbo.sysmail_profileaccount pa
         ON p.profile_id = pa.profile_id
       JOIN msdb.dbo.sysmail_account a
         ON pa.account_id = a.account_id
       JOIN msdb.dbo.sysmail_server s
         ON a.account_id = s.account_id
 

Following is the output:

以下是输出：

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

如果由于某种原因在设置数据库邮件时遇到麻烦–请查看有关此主题的以下详细文章： 如何在SQL Server中配置数据库邮件

DDL触发器的T-SQL脚本 (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:

当在SQL Server数据库上发出ALTER DATABASE命令时，将执行DDL触发器。 触发器从EVENTDATA（）函数收集命令，processID，时间和详细信息，并将其存储在名为#TempAudit的临时表中。 看下面的代码 片段：

create table #TempAudit (Command varchar(50),SQLCommand varchar(5000),LoginName varchar(100),DBName varchar(500))
Insert into #TempAudit
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,
   EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  ,
   EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')  ,
   EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')  

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:

为了以表格格式显示T-SQL命令，SQL查询，LoginName和数据库 名称 ，我们创建了带有HTML标记的动态SQL查询，并将查询保存在@tableHTML变量中。 请参见以下代码片段：

DECLARE @subject NVARCHAR(max)
DECLARE @tableHTML NVARCHAR(max)
 
SET @subject = 'Critical Alert: Database State has been changed on : '
               + @@servername
      SET @tableHTML =
  '     <html><Body><style type="text/css">table {font-size:9.0pt;font-family:verdana;text-align:left;}tr {text-align:left;} 
  h3 {display: block;font-size: 15.0pt;font-weight: bold; font-family: verdana;        text-align:left;       } </style><H3>Critical Alert: Database State has been changed on '+ @@servername + '</H3>' + N'<table border="1">'
  +N'<tr><th>Command</th><th>SQL Query</th><th>Command Executed By</th><th>Database Name</th></tr>'
                 + Cast((SELECT 
         Command AS 'TD', '', 
         SQLCommand AS 'TD', '',
         LoginName AS 'TD', '', 
         DBName AS 'TD', '' 
         FROM #TempAudit FOR xml path ( 'tr' ), root) AS NVARCHAR(max))
                 + N'</table>      </html>     </Body>'
 

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

使用SQL Server数据库邮件，它将数据发送到所需的收件人。 请参见以下代码片段：

EXEC msdb..Sp_send_dbmail
  @profile_name = 'TestDBMail',
  @recipients = 'nisargupadhyay87@outlook.com',
  @subject = @subject,
  @importance = 'High',
  @body = @tableHTML,
  @body_format = 'HTML';  
Drop table #TempAudit

Following is the entire script of the database trigger:

以下是数据库触发器的整个脚本：

IF EXISTS (SELECT * FROM sys.server_triggers  
    WHERE name = 'ddl_trig_changedbstat')  
DROP TRIGGER ddl_trig_changedbstat  
ON ALL SERVER;  
GO  
CREATE TRIGGER ddl_trig_changedbstat   
ON ALL SERVER   
FOR ALTER_DATABASE   
AS   
create table #TempAudit (Command varchar(50),SQLCommand varchar(5000),LoginName varchar(100),DBName varchar(500))
Insert into #TempAudit
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,
   EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  ,
   EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')  ,
   EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')  
DECLARE @subject NVARCHAR(max)
DECLARE @tableHTML NVARCHAR(max)
 
SET @subject = 'Critical Alert: Database State has been changed on : '
               + @@servername
      SET @tableHTML =
  '     <html><Body><style type="text/css">table {font-size:9.0pt;font-family:verdana;text-align:left;}tr {text-align:left;} 
  h3 {display: block;font-size: 15.0pt;font-weight: bold; font-family: verdana;        text-align:left;       } </style><H3>Critical Alert: Database State has been changed on '+ @@servername + '</H3>' + N'<table border="1">'
  +N'<tr><th>Command</th><th>SQL Query</th><th>Command Executed By</th><th>Database Name</th></tr>'
                 + Cast((SELECT 
         Command AS 'TD', '', 
         SQLCommand AS 'TD', '',
         LoginName AS 'TD', '', 
         DBName AS 'TD', '' 
         FROM #TempAudit FOR xml path ( 'tr' ), root) AS NVARCHAR(max))
                 + N'</table>      </html>     </Body>'
EXEC msdb..Sp_send_dbmail
  @profile_name = 'TestDBMail',
  @recipients = 'nisargupadhyay87@outlook.com',
  @subject = @subject,
  @importance = 'High',
  @body = @tableHTML,
  @body_format = 'HTML';  
Drop table #TempAudit
GO  

测试DDL触发器 (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:

为了测试触发器，我们将DBA数据库更改为SQL Server单用户模式。 以下T-SQL查询将DBA数据库的状态更改为SQL Server单用户模式：

Alter database [DBA] set single_user with rollback immediate

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:

成功执行命令后，数据库模式将更改为单用户，并且我在邮箱中收到一封电子邮件通知。 请看以下屏幕截图：

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.

在本文中，我已经解释了有关DDL触发器 ， SQL Server单用户模式和EVENTDATA（）函数的信息。 我还介绍了DDL触发器，该触发器可用于在任何用户更改数据库模式时发送电子邮件警报。

翻译自: https://www.sqlshack.com/send-an-alert-when-the-database-state-changes-to-sql-server-single-user-mode/