Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Rob Lobbe,
Using the Service Broker to capture DDL_EVENTS we can collate when and who has changed aspects of the server and/or databases. (See Books Online for what events DDL_EVENTS will provide)
This script should be run in a 'DBA' database (not an application one). Once executed it is set-n-forget.
You will need the Service Broker enabled.
The major point of call is the iSpy.Notification_Detail view.
Selecting from this view will provide an audit of all server and database object changes.
Activities that become highlighted
(non)Temporary tables - Tables that get created then dropped repeatedly
Suck-it-and-see development - Dev's with excessive drop&create and/or alter on the same database object
Missing Grants - Typical an object will be created, followed by a security grant (you see when the pattern is broken)
Vendor Upgrades - for when they won't tell you what they are doing, see for yourself.
Who's to blame - Sometimes it's important to know when and who made a change (especially with vendors) but also when to keep your mouth shut as whooops... [nobody does that? right? we all stick our hand up, and admit our own mistakes, well...] You now have the evidence - one way or the other.
Remove the data_compression options.
Datetime is needed instead of datetime2 (for SQL2005)
IIRC MD5 may be needed for SQL2005 (rather than SHA1)
Technical (not-so obvious)
Trigger - this code could just as easliy been included in the iSpy.Reader - my design choice here was to allow the timestamp to be a primary key, this meant that I had to work around the granularity of dates - when lots of small changes happen rapidly (see TryInsert: loop). It also avoids the inherent limitations of integer datatypes (identity) when rapid create/drop activity is encounted - bigint would survive, but is still limited
Trigger - futher using a trigger you can also load a 'backlog' of catured EVENTDATA() into the iSpy.Notifications table - handy for maintaining a history when a system migration is required.
Hashbytes is used to identify 'identical' code - unfortunately this is restricted to the first 8000 characters of a SQL command (lengthy procedures will only keep the 'original' version - so you won't see individual change with these - but you will still see than changes have occured) - a CLR function can get around this limitation.
<SERVER> events, inparticular Security events, may not have all the information available (eg DatabaseName or Password). The Audit of these events is more important than the content. Should you want you could specifically detect these events and extend the functionality to meet your requirements.
DoOver - (If you uncomment the "drop" section)
The script is harsh - All iSpy Objects are dropped before being created - this means that ALL data is also dropped. It also means you should be careful that you don't already have any object with a corresponding name before running the script.
How notificate create database event
This post tells you how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database object...
AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event for a failed event
I am trying to create job-specific database objects (views, sp) after creating the db and tables suc...
Undocumented Capabilities of Extended Event Objects
The extended event objects (objects exposed by ...