How to turn on DDL Auditing?

Question

How to turn on DDL Auditing?

SQLdevotee

SSCarpal Tunnel

Points: 4820
More actions
October 30, 2008 at 8:56 am

#127334

Guys, I was planning to turn on DDL auditing to monitor my servers better. Is there any way to do this?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Define what you mean by DDL Auditing? You can easily monitor most DDL commands with DDL Triggers in SQL Server 2005. There are plenty of articles out there that cover how to actually implement them. The BOL topics listed below are good references for them. I use DDL Triggers in some development work to help developers track the changes that they have made along their development cycle, which helps them to better package them for deployment into QA and or Production.

DDL Triggers

http://msdn.microsoft.com/en-us/library/ms190989.aspx

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 2

What I meant was like when you want to see if there are failed logins in the logs, you basically go to the server and then right click properties. From there checking the audit failed logins only.

So, I want to do something similar for DDL actions. I just need to know if something was done by my developers, it should log it in my logs as well. Thats all.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

You would still need to create a DDL Trigger for the actions you want to audit, and then you can use RAISERROR and specify the WITH LOG option to send it to your logs. There is no other mechanism that will do what you are asking.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Check this once:

http://www.sqlservercentral.com/articles/Auditing/62126/

DDL triggers will help you to audit database/server level ddl statements.

MJ

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 5

Well, since there is no way other than loading the scripts in the server itself, I shall try it then.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

There's nothing similar to audit logins for anything else. If you want to track DDL changes, you'll have to use a DDL trigger or a server-side trace that's permanently running. The triggers are easier to use and have less overhead.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass