How to find database objects created with SQL default trace

10 October,2019 by Jack Vamvas

How can you find information about SQL database objects created? We're talking about CREATE DATABASE,CREATE INDEX,CREATE PROCEDURE,CREATE DATABASE. 

There are common scenarios where you may require these details. For example:

1) It is the organisation's sql server security policy for any production database to progress through a change request and review process. Monitoring database objects added , can identify developers attempting to avoid the review process. 

2) Attempting to identify rogue processes. For example - software added to manage the server introduces new database processes - such as database tables 

3) Troubleshooting an outage. An unexpected  service impact occurs and there is a suspicion some database objects have changed. Using this script will give you some details 

 

A few things to note about this sql script:

1) Using the eventclass=46. That equates to the Object:Created   default trace event class. 

2)I've excluded   objectnames with a value "telemetry_xevents", as this process does generate a  relatively high amount of database objects - which leads to white noise. Adjust according to your needs , and include\exclude as required 

 

select e.name as eventclass,
t.loginname, 
t.spid, 
t.starttime,
t.textdata, 
t.objectid, 
t.objectname, 
t.databasename, 
t.hostname, 
t.ntusername, 
t.ntdomainname, 
t.clientprocessid, 
t.applicationname, 
t.error 
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value] 
FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT) T 
inner join sys.trace_events e on t.eventclass = e.trace_event_id 
where eventclass=46 and objectname <> 'telemetry_xevents' 
order by objectname


Read more on default trace 

How to get user security changes with SQL default trace

Trace flag 610 and High performance data loading

How to use sxstrace.exe – diagnose side-by-side errors

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find database objects created with SQL default trace


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer