10 October,2019 by Tom Collins
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |