Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Monitoring Changes in Your Database Using DDL Triggers Expand / Collapse
Author
Message
Posted Tuesday, October 7, 2008 3:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Slightly slow afternoon, so I decided to go exploring. My gut feeling is that it won't work in 2005 or 2008, but I love working with system objects and really want to find a way to do it!

I wonder if running a log viewer tool might let you capture that info, or perhaps a trace somehow filtered to see only DDL statements.

:D
Post #582231
Posted Tuesday, October 7, 2008 4:15 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Giovanny Gomez Convers (10/7/2008)
Hi!,
When I try delete a user on database, appears the next error message:

"SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (.Net SqlClient Data Provider)"

The Trigger is the reason for this message?

tks.

I have no problem adding or deleting a user to my DDL audited database. I'd suggest two things. First, check your audit table to make sure the data types are the same as in the article. You might want to change any char or varchar to nchar or nvarchar. Second, look up ARITHABORT in BOL, there are some database-specific configurations that can cause it to abort a transaction.

Good luck!
Post #582252
Posted Tuesday, October 7, 2008 4:49 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 9, 2012 10:26 AM
Points: 891, Visits: 1,958
Carla Wilson (10/7/2008)
Wayne,
Wow, good job investigating system tables/views and triggers! (although I did not expect you to drop everything to do it - - I was just reply to your question.)
Your testing is much appreciated.

Finished the 2005 attempt: fail with the following message:

Msg 8197, Level 16, State 6, Procedure tr_syscolumns, Line 1
Object '_syscolumns' does not exist or is invalid for this operation.

Got the same error before trying to slide past it with view.

It seems to me that it should be possible. I glanced at this article here on SSC, so maybe it's possible, perhaps I just need to work at it a bit more.

:D
Post #582266
Posted Monday, March 30, 2009 1:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 9:50 PM
Points: 119, Visits: 650
HI All,

As per the article I have created the AUDITLOG table and also created the trigger.

But when executed some DDl commands the AUDITLOG table is not working as expected.

CREATE TABLE dbo.test55(col INT)
GO
DROP TABLE dbo.test55
GO
-- View log table
SELECT *
FROM dbo.AuditLog

ID Command PostTime HostName LoginName
----------- ------------------------------ -------
1 NULL NULL ROOM-5E8A79FFB4 NULL
(1 row(s) affected)

My query is why the command column is not able to capture any event.Has something need to be done to capture the events.


Kindly assist me.




Thanks,
Sandhya
Post #686429
Posted Tuesday, March 31, 2009 7:45 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:26 PM
Points: 676, Visits: 433
What is the declaration of your trigger?

By the NULLs, it appears that nothing is being pulled from EVENTDATA()



Post #687040
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse