Audit all databases.

Question

Audit all databases.

Steve Jones - SSC Editor

SSC Guru

Points: 736270
More actions
September 4, 2013 at 10:51 am

#298529

Show how someone can use a DDL trigger to audit all CREATE TABLE statements in all databases on a server.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

I can tackle this. I have a good one in place in all of my databases. The script has been passed around the block many times.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736270

September 4, 2013 at 12:02 pm

#1647438

Great. Thanks and submit it when you can.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736270

January 31, 2014 at 11:15 am

#1685163

bump

EricEyster SSCrazy Points: 2974 More actions · Answer 4

EricEyster

SSCrazy

Points: 2974

February 28, 2014 at 1:37 pm

#1693261

I will give it my best rookie attempt.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

Thanks, after about 3-4 weeks, I'll bump this for a reminder

EricEyster SSCrazy Points: 2974 More actions · Answer 6

sorry for the delay. the rough draft is complete. I will submit be end of the week.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 7

Steve Jones - SSC Editor (9/4/2013)
Show how someone can use a DDL trigger to audit all CREATE TABLE statements in all databases on a server.

Somehow I feel like there is more to this question, but a server level DDL trigger can do what you're asking.

http://technet.microsoft.com/en-us/library/ms186406(v=SQL.105).aspx

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR CREATE_DATABASE

AS

...

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

EricEyster SSCrazy Points: 2974 More actions · Answer 8

Eric M Russell (3/19/2014)
Steve Jones - SSC Editor (9/4/2013)
Show how someone can use a DDL trigger to audit all CREATE TABLE statements in all databases on a server.
Somehow I feel like there is more to this question, but a server level DDL trigger can do what you're asking.
http://technet.microsoft.com/en-us/library/ms186406(v=SQL.105).aspx
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
...
...

Agreed, and that is what I use on our environments. There is some value in separate triggers for business logic,etc, but if you are just logging the event the "all" approach is the way to go. I included and index change in my test script to demonstrate that you may not get what you expect unless you read the chart correctly.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

Having an article that explains how this works, more than BOL reference, is the request. If you want to do this at a database level as well, please feel free.