Monitoring Changes in Your Database Using DDL Triggers

Question

Monitoring Changes in Your Database Using DDL Triggers

Viewing 15 posts - 16 through 30 (of 44 total)

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

yu.he SSC Enthusiast Points: 139 More actions · Answer 1

I am using SQL Server 2000, and the script for creating the Trigger doesn't work. I got "Incorrect syntax near the keyword 'Database'.". Is there any way I can apply this logic to SQL Server 2000?

Thanks.

Harry Drenth Say Hey Kid Points: 678 More actions · Answer 2

Harry Drenth

Say Hey Kid

Points: 678

October 2, 2008 at 8:32 am

#879984

don't think so

David Dye SSC Veteran Points: 273 More actions · Answer 3

Unofrtunately DDL triggers are only available in SQL 2005 or higher.

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 4

HD,

I really like the extra columns you have included for Database name, Object name, etc.

Thanks for sharing!

Timothy Lenz SSC Veteran Points: 206 More actions · Answer 5

Hi David,

I like the idea of the article but had a problem when I put it on our developer machine

after running your test the results I got from other developers came out like this:

IDCommandPostTimeHostNameLoginName

1UPDATE STATISTICS tbluser 2008-10-02T08:51:07.563NSSLT5NSSDEVSQL\rcsnslenzti

2CREATE TABLE dbo.Test(col INT) 2008-10-02T08:51:07.640NSSLT5NSSDEVSQL\rcsnslenzti

3DROP TABLE dbo.Test 2008-10-02T08:51:07.670NSSLT5NSSDEVSQL\rcsnslenzti

4--ENCRYPTED--2008-10-02T09:49:14.767NSSDEVWEBNSSDEVSQL\RCSNETUSER

5--ENCRYPTED--2008-10-02T09:49:14.827NSSDEVWEBNSSDEVSQL\RCSNETUSER

6--ENCRYPTED--2008-10-02T09:49:14.843NSSDEVWEBNSSDEVSQL\RCSNETUSER

7--ENCRYPTED--2008-10-02T09:49:14.877NSSDEVWEBNSSDEVSQL\RCSNETUSER

8ALTER TABLE dbo.tblVIFixes ADD test nchar(10) NULL 2008-10-02T11:47:51.767NSSLT5NSSDEVSQL\rcsnslenzti

I ran a check by opening a table and adding a column test and then ran the query again. This gave me the newest line 8

My question is what would give the --ENCRYPTED-- response?

I hope to get this working to track changes undocumented by developers!

Thanks,

Tim

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

The enrcypted items are Create Statistics done by the server, and create function and stored procedure that are done by the system....

I include the EventType from the XML, which makes it possible to query what a command was. Sample output, not all columns so as not to give anything away 🙂

PostTime Event

---------------------------------------------------------------2006-12-06 13:15:47.173 CREATE_STATISTICS

2006-12-06 13:15:47.203 CREATE_STATISTICS

2006-12-14 11:51:59.950 ALTER_FUNCTION

2006-12-17 04:17:27.270 ALTER_INDEX

Above is just a small sample of course.... my table that stores this has 4.2 million records 🙂

And yes, it has proved beyond valuable in tracking down changes in the system. Also in actually rolling back a change that was not checked into VSS.

Naresh Rohra Valued Member Points: 65 More actions · Answer 7

Is this possible in MSDE version of SQL Server 2000 family?

Anipaul SSC-Insane Points: 24681 More actions · Answer 8

Anipaul

SSC-Insane

Points: 24681

October 3, 2008 at 2:08 am

#880336

Nice article, very well explained 🙂

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 9

naresh.rohra (10/2/2008)
Is this possible in MSDE version of SQL Server 2000 family?

DDL Triggers are only available in 2005 and above.

deepesh.verma Grasshopper Points: 14 More actions · Answer 10

When I use this AuditLog table and view the data, most of the times I see --ENCRYPTED-- in command field.

Can you please explain what does it mean?

Thanks,

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 11

The likely cause of ENCRYTPED is explained on a response on page 2.

Wayne West SSC-Insane Points: 22586 More actions · Answer 12

If you wanted to live dangerously, you could implement something like this in SQL Server 2000, probably even MSDE, though I'm not sure I'd try it. Basically, at a minimum level, apply a trigger monitoring events in the sysobjects, syscolumns, and syscomments tables. Every object in a database has an entry in sysobjects. If it's a table, it will have one entry in syscolumns for every field in the table. If it's a view or stored procedure, it'll have an entry in syscomments (obviously if you encrypt your procs you won't see anything in clear text in the audit). You might also want to monitor sysforeignkeys, sysindexes, and sysindexkeys. Hey, what the heck! Go all the way and also monitor sysusers!

The downside, obviously, is needing one trigger per table, and I don't know if the XML code that David provides in his article would work in 2000, you might have to write a custom trigger for each table. You might be able to get away with one audit table by coding the table name into the trigger, but that's still 20 triggers if you wanted to do all tables, at least on the DB that I'm looking at right now.

I wouldn't recommend it because I'm not comfortable doing anything to system objects. Adding a trigger is somewhat non-intrusive: it's attached to the object, rather than modifying the object, but I'd still be uncomfortable about it. At the same time, these aren't events that fire on a frequent basis, even when you're developing a DB they're going to fire in spurts, so I don't think you'd take a big performance hit.

Obviously if you were to do something like this you ought to experiment with a test system before even considering applying it to a production system.

Oh, what the heck. I just installed SQL Server 2008 on my MacBook Pro, maybe I'll install 2000 on it tonight and play with system object triggers.... :hehe:

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Wayne West SSC-Insane Points: 22586 More actions · Answer 13

Oh, and David: excellent job! Nice code, short and fairly simple, good job describing it. Well done.

We're 90ish% a 2000 shop, I set up our first two 2005 servers in the last few months and I've been intending to set up something like this. I look forward to seeing the results.

Kudos to you, sir! 😀

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

db_bunny Right there with Babe Points: 763 More actions · Answer 14

Thanks for a fantastic DDL tracking tool. I wrote a similar one using server wide trigger auditing logon events.

Is there a way to stop the deletes coming through as '--ENCRYPTED--' rather than the TSQL command?

Thank you,

Joanna

Wayne West SSC-Insane Points: 22586 More actions · Answer 15

You might double-check your code, Joanna, the only time my audit table shows --ENCRYPTED-- is when I create an encrypted view. I assume it would do likewise for any other encrypted object. If I do this:

create view vwPhoneList

as select * from phonelist

drop view vwPhoneList

create view vwPhoneList

with encryption

as select * from PhoneList

drop view vwPhoneList

I see this:

ID Command PostTime

----------- ----------------------------------- ------------------------

6 create view vwPhoneList ... 2008-10-06T08:24:46.280

7 drop view vwPhoneList 2008-10-06T08:25:02.827

8 --ENCRYPTED-- 2008-10-06T08:25:23.283

9 drop view vwPhoneList 2008-10-06T08:25:30.657

(9 row(s) affected)

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]