How to know which application or user put the SQL Server Database in single user mode

In my case, there is SQL Server Database which is used by multiple teams. The database has suddenly gone into Single User mode. Now, how to identify the responsible user or application who did that? It could be an application as well.

If not possible now, then what to do to capture this info in the future?

The spid is recorded in the SQL Server error log, but not the user / application / host. And, as Grant mentioned, nothing is recorded to the built-in system_health session (or the default trace, if you still have that enabled).

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

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


In order to catch this information in the future, you could set up an Extended Events session, e.g.

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;

From right-clicking the session under Management > Extended Events > Sessions, and choosing Watch Live Data (or going to the event file and choosing View Target Data...) you can see events like this (note that the target database is actually listed under object_name; database_name is the context database):

But you will also get all other alter database events. I'll leave it as an exercise to the reader to pull the data programmatically (and filter) using sys.fn_xe_file_target_read_file (docs here). Grant Fritchey has a starter query here.

There are other options, too, like SQL Server Audit / DATABASE_OBJECT_CHANGE_GROUP.

StackExchange Database Administrators Q#299624, answer score: 13