How to identify who made a change [duplicate]

Question

I noticed the following in the sql server log, is there any way to determine the login/user that initiated this change. It appears a database was set to single user mode and back to multi user. Here is the log snippet

Date       10/10/2017 8:55:27 PM
Log         SQL Server (Current - 10/11/2017 8:00:00 AM)
Source      spid139
Message     Setting database option MULTI_USER to ON for database 'DBNAME'

Answer 1

SQL server has "default trace" running by default. You need to search the trace files using something like this:

--Find current trace file name
--Select * from sys.traces  

SELECT 
     loginname,
     loginsid,
     spid,
     hostname,
     applicationname,
     servername,
     databasename,
     objectName,
     e.category_id,
     cat.name as [CategoryName],
     textdata,
     starttime,
     eventclass,
     eventsubclass,--0=begin,1=commit
     e.name as EventName
FROM ::fn_trace_gettable('H:\MSSQL10_50.ODS01\MSSQL\Log\log_217.trc',0)
     INNER JOIN sys.trace_events e
          ON eventclass = trace_event_id
     INNER JOIN sys.trace_categories AS cat
          ON e.category_id = cat.category_id

Answer 2

I'm a huge fan of the event-driven approach for a scenario like yours. Meaning, I don't want to sleuth who did it after the fact, I want SQL to tell me when it happens. For that reason, I'd suggest a DDL Trigger. Here's an example:

CREATE TRIGGER trgAlterDatabase 
ON ALL SERVER 
FOR ALTER_DATABASE 
/*****************************************************************************
* Name     : trgAlterDatabase
* Purpose  : Sends an email to the dba when a database is altered.
* Inputs   : None
* Outputs  : None
* Returns  : Nothing
******************************************************************************
* Change History
*   07/03/2014  DMason  Created
******************************************************************************/ 
AS 
    DECLARE @TsqlCmd NVARCHAR(MAX);
    DECLARE @Login NVARCHAR(MAX);
    DECLARE @Subj NVARCHAR(255) ;
    DECLARE @MailBody NVARCHAR(MAX);

    SET @TsqlCmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)');
    SET @Login = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(MAX)');
    SET @MailBody = 
        'TSql Command: ' + @TsqlCmd + CHAR(13) + CHAR(10) +
        'Login Name: ' + @Login;
    SET @Subj = @@SERVERNAME + ' - Database Altered';

    --Email the dba.
    EXEC msdb..sp_send_dbmail
        @recipients = 'TheDba@YourDomain.com', 
        @Subject = @Subj,
        @body = @MailBody;

    --Other options: 
    --Log to a table?
    --Raise a custom error that is handled by SQL Agent?

Additional Info - SQL Server Event Handling: DDL Events