AFTER DDL ON DATABASE Trigger

Question

AFTER DDL ON DATABASE Trigger

Stevie T

Right there with Babe

Points: 797
More actions
August 8, 2003 at 2:14 pm

#81649

In Oracle its possible to place a trigger at a database level. What we do is track DDL changes and add to an audit table. Is there an equivalent process I can use for SQL Server?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

There's built-in C2 Auditing, which I suppose is a bit over the top for your purposes, or the alternative is writing and scheduling a continuous server-side Profiler trace with customer events selected. Various events availavble to Profiler cover DDL stuff.

Cheers,

- Mark

Cheers,
- Mark

Stevie T Right there with Babe Points: 797 More actions · Answer 2

thanks Mark. As you mentionned is a little bit of over kill but it could work. Is there anything I can do on the sysobjects table?

mccork SSC-Insane Points: 22288 More actions · Answer 3

'fraid not Steve. Trigger on system tables aren't supported.

However, I do have plans to write a (customisable) auditing procedure. If I pull my finger out I might get it done this weekend.... um, er, better make that this month.

Cheers,

- Mark

Cheers,
- Mark

Stevie T Right there with Babe Points: 797 More actions · Answer 4

cheers mark -> thought that was the case on the sysobjects but never did confirm. would be a cool feature mr. gates (!?)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi Mark,

quote:
There's built-in C2 Auditing, which I suppose is a bit over the top for your purposes, or the alternative is writing and scheduling a continuous server-side Profiler trace with customer events selected. Various events availavble to Profiler cover DDL stuff.

the main problem with C2 auditing is, that you are not able to select what you want to be audited and what not. To me it seems like an all or nothing approach, and as such certainly an overkill.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mccork SSC-Insane Points: 22288 More actions · Answer 6

G'day Frank.

Absolutely. I turned it on once, for 5 minutes, just to see what we got. Yuk!

BTW: I'm no longer getting email notifications "whenever anyone replies to a topic". Are you?

Cheers,

- Mark

Cheers,
- Mark

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

quote:
BTW: I'm no longer getting email notifications "whenever anyone replies to a topic". Are you?

no!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]