主页 / dba / 问题 / 299624
Accepted
Murali Dhar Darshan
Asked: 2021-09-16 04:39:38 +0800 CST

如何知道哪个应用程序或用户将 SQL Server 数据库置于单用户模式

  • 772

就我而言，有多个团队使用的 SQL Server 数据库。数据库突然进入单用户模式。现在，如何确定执行此操作的负责用户或应用程序？它也可以是一个应用程序。

如果现在不可能，那么将来如何捕获这些信息？

sql-server locking

3 个回答

  1. spid 记录在 SQL Server 错误日志中，而不是用户/应用程序/主机。而且，正如格兰特所提到的，内置system_health会话（或默认跟踪，如果您仍然启用）不会记录任何内容。

    EXEC sys.sp_readerrorlog 0, 1, 'single_user';

    结果：

    LogDate                   ProcessInfo   Text
-----------------------   -----------   ------------------------------------
2021-09-15 08:54:02.227   spid60        Setting database option SINGLE_USER 
                                        to ON for database 'blat'.

    为了在将来捕获此信息，您可以设置扩展事件会话，例如

    CREATE EVENT SESSION [CatchAlterDatabase] ON SERVER 
ADD EVENT sqlserver.object_altered
(
  ACTION
  (
    sqlserver.client_app_name, sqlserver.client_hostname, 
    sqlserver.server_principal_name, sqlserver.sql_text
  ) WHERE ([object_type] = 'DATABASE')
)
ADD TARGET package0.event_file
(
  SET filename = N'CatchAlterDB.xel', 
    max_file_size=(25), max_rollover_files=(10)
)
WITH 
(
  MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
  TRACK_CAUSALITY=OFF,STARTUP_STATE=ON
);
GO

ALTER EVENT SESSION CatchAlterDatabase ON SERVER STATE = START;

    右键单击 Management > Extended Events > Sessions 下的会话，然后选择 Watch Live Data（或转到事件文件并选择 View Target Data...），您可以看到类似这样的事件（请注意，实际上列出了目标数据库在object_name;下database_name是上下文数据库）：

    在此处输入图像描述

    但是您还将获得所有其他的更改数据库事件。我将把它作为练习留给读者使用sys.fn_xe_file_target_read_file(docs here)以编程方式（和过滤）提取数据。格兰特弗里奇在这里有一个入门查询。

    还有其他选项，例如SQL Server Audit / DATABASE_OBJECT_CHANGE_GROUP。

    • 13
  2. Best Answer

    现代 SQL Server 版本将允许您创建服务器安全审计。它们有点像扩展事件，但更精简。

    设置 SQL Server 安全审核的基本脚本如下所示：

    创建服务器安全审计

    USE [master]
GO


/****** Object:  Audit [ServerSecurityAudit2021]    Script Date: 03.03.2021 12:39:07 ******/
CREATE SERVER AUDIT [ServerSecurityAudit2021]
TO FILE 
(   FILEPATH = N'E:\MSSQL13.INSTANCENAME\MSSQL\Log\'
    ,MAXSIZE = 256 MB
    ,MAX_FILES = 10
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

    指定安全审计详细信息

    USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerSecurityAuditSpecs2021]
FOR SERVER AUDIT [ServerSecurityAudit2021]
ADD (DATABASE_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP)
-- , ADD (USER_CHANGE_PASSWORD_GROUP)
-- , ADD (SERVER_PERMISSION_CHANGE_GROUP)
-- , ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
-- , ADD (LOGIN_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO

    我注释掉了一些 SQL Server 审计操作组和操作。可以在SQL Server 审核操作组和操作(Microsoft | SQL Docs)下找到其中的列表

    打开安全审计

    ALTER SERVER AUDIT [ServerSecurityAudit2021] WITH (STATE = ON)
GO

    参考： CREATE SERVER AUDIT (Transact-SQL) (Microsoft | SQL Docs)

    使用 SSMS

    您可以按照创建服务器审核和数据库审核规范(Microsoft | SQL Docs)中概述的步骤对 SSMS 执行相同操作。

    查看服务器审计

    在您的实例的 SSMS 中，导航到Security | 审计 | Your_Audit_Name并右键单击审计并选择查看审计日志。

    SSMS 查看审核日志

    然后，您可以选择ALTER Action ID，它将显示发生的详细信息。

    审核日志详细信息

    编辑细节以保护无辜者

    • 9

  3. 事后你无法知道。我查了一下，system_health 没有捕捉到这些信息。但是，您可以设置扩展事件以在将来准确捕获这一点。我在这里有审核数据库更改的示例。我没有显示将其设置为 SINGLE_USER，但我更改兼容性级别的示例绝对适用。

    • 8

相关问题