There are a couple methods of getting recent schema changes out of SQL Server without building an auditing process. The first
one is using the Default trace (assuming it is enabled) and the second one is reading the Transaction Log file. The default trace
will rollover to a new file name thus if you want to log all changes you would need to setup some automated logging process for that.
Default Trace using T-SQL:
Step 1: Check the Default Trace is enabled
sp_configure
name minimum maximum config_value run_value
default trace enabled 0 1 1 1
Step 2: Get the Default Trace file path & name
SELECT start_time, last_event_time, is_default, max_files, max_size, is_rollover, event_count, [path] FROM sys.traces where is_default=1
This default trace will keep up to 5 files before they are purged.
Step 3: Execute T-SQL (insert the file path and name from Step 2 below)
SELECT
f.StartTime as 'Record Start Time'
,f.DatabaseName
,f.objectname
,f.LoginName
,CASE f.eventclass WHEN 46 THEN convert(varchar(100),'Object:Created')
WHEN 47 THEN convert(varchar(100),'Object:Dropped')
WHEN 164 THEN convert(varchar(100),'Object:Altered')
ELSE convert(varchar(100),f.eventclass)
END AS 'Event Class'
,CASE f.EventSubClass WHEN 0 THEN convert(varchar(100),'BEGIN')
WHEN 1 THEN convert(varchar(100),'COMMIT')
WHEN 2 THEN convert(varchar(100),'ROLLBACK')
ELSE convert(varchar(100),f.EventSubClass)
END AS 'Event SubClass'
,f.NTDomainName
,f.NTUserName
,f.HostName
,f.EventSequence
,f.TransactionID
,f.XactSequence
--,f.*
FROM sys.traces t
CROSS APPLY fn_trace_gettable('E:\MSSQL12.MSSQLSERVER\MSSQL\Log\log_10677.trc', DEFAULT) f
WHERE t.is_default = 1
AND f.DatabaseName not in ('tempdb','master')
AND f.ObjectType <> '21587' --Statistics
AND EventClass IN (46,47,164)
ORDER BY f.StartTime DESC, f.EventSequence DESC
Default Trace using SSMS:
Step 1: SQL Server Management Studio - Reports - Standard Reports - Schema Changes History
Read Transaction Log File
select [Begin Time] as 'BeginTime', [Transaction Name], [Transaction SID], Operation
from fn_dblog(null, null)
where Operation in ('LOP_BEGIN_XACT')
order by 'BeginTime' desc;
Transaction SID matches to SID below:
Select [sid] from sys.syslogins where name like '%%'
|
|
|
|
|
|