Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)

iSpy (on your development)

By Rob Lobbe,

Description

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)

Usage

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.

Non-Enterprise/SQL2005

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.

Total article views: 1240 | Views in the last 30 days: 8
 
Related Articles
FORUM

How notificate create database event

How notificate create database event

ARTICLE

Change the Settings of a Database Object Using Powershell

This post tells you how to change the QUOTED_IDENTIFIER and ANSI_NULLS settings of a database object...

FORUM

AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event

AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Produces Failed and Successful Event for a failed event

BLOG

Undocumented Capabilities of Extended Event Objects

Undocumented Capabilities of Extended Event Objects The extended event objects (objects exposed by ...

FORUM

Creating Database Objects from a Trigger-invoked stored procedure....

I am trying to create job-specific database objects (views, sp) after creating the db and tables suc...

Tags
audit    
ddl_events    
development    
eventnotification    
events    
notifications    
schemas    
sql server 2008    
sql server 2008 r2    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones