October 6, 2008 at 9:36 am
I'm seeing something interesting, and was wondering if someone can tell me what's going on. As an experiment to see what would appear in the log, I modify a table called PhoneList to add one character to a name field and I see the following sequence in my audit table:
17 ALTER TABLE dbo.PhoneList DROP CONSTRAINT FK_...
18 CREATE TABLE dbo.Tmp_PhoneList ...
19 GRANT SELECT on dbo.Tmp_PhoneList To Public as dbo
20 DROP TABLE dbo.PhoneList
21 CREATE CLUSTERED INDEX IX_PhoneList ON dbo.PhoneList ...
22 ALTER TABLE dbo.PhoneList ADD CONSTRAINT FK_...
23 CREATE TRIGGER tr_PhoneList ON dbo.PhoneList FOR ...
There has to be an implied 20.5 where the Tmp_PhoneList table is renamed to PhoneList, but the trigger doesn't catch it.
Another interesting thing is the dialog box in SSMS is saying that it will be saving the table referenced in the FK, but the trigger is not catching that. I'm assuming there that SSMS is initially thinking that the FK table needs to be saved, but when it comes time to run the query, the database engine realizes that it doesn't have to be and doesn't do anything.
Any thoughts about the missing rename? Is somehow the audit trigger missing it?
-----
[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]
October 6, 2008 at 10:59 am
Yes, I noticed the same thing when I tested the audit program on a test database. I don't know why the "rename" step (or whatever the system REALLY does) is not in the audit.
However, I was a little disappointed to see that what I THOUGHT would be an audit of a column change turned out to be an audit of a change to the table.
While I can understand that column changes might happen by a different route than what I would have thought, column changes are the type of audit that I really wanted to track, so this method turns out to not help me much.
October 6, 2008 at 11:48 am
Carla, have you considered experimenting with a trigger on the information_schema.columns view? BOL says you can apply triggers to views, but I don't know if you can bind them to system views.
-----
[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]
October 7, 2008 at 12:59 pm
The attached code works beautifully, except we find that we can no longer use "SET ANSI_PADDING OFF" and must convert to "SET ANSI_PADDING ON", see error message below.
I like to understand why would this be the case?
thanks!
error message:
Msg 1934, Level 16, State 1, Procedure DDLChange_trg, Line 29
INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.
October 7, 2008 at 1:41 pm
Pieter,
The Error message implies that you can't use SET ANSI_PADDING OFF with XML data methods, and this script uses an XML datatype, but I couldn't find a good explanation in BOL.
Wayne,
Wayne West (10/3/2008)
If you wanted to live dangerously...I wouldn't recommend it because I'm not comfortable doing anything to system objects.
...
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:
I thought YOU were the one who was going to live dangerously! 😉 - - I have not been bold enough to test this!
October 7, 2008 at 2:00 pm
thanks Wayne!
We do data warehousing instead of OLTP and like the space savings associated with "set ansi_padding off". Is there a way that we can make it work and have the audit trail?
Thanks,
Piet
October 7, 2008 at 2:17 pm
Pieter (10/7/2008)
thanks Wayne!We do data warehousing instead of OLTP and like the space savings associated with "set ansi_padding off". Is there a way that we can make it work and have the audit trail?
Thanks,
Piet
Sorry, can't help you there, Piet. I'm strictly an OLTP guy, I don't work with DW at all.
Myself, I much prefer ANSI padding off, I hate having to trim fields before concatenating.
-----
[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]
October 7, 2008 at 2:58 pm
Carla Wilson (10/7/2008)
Wayne West (10/3/2008)
If you wanted to live dangerously...I wouldn't recommend it because I'm not comfortable doing anything to system objects.
...
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:
I thought YOU were the one who was going to live dangerously! 😉 - - I have not been bold enough to test this!
Yeah, yeah, yeah. 🙂
OK, I spent half an hour in SS2000 trying to make a trigger on syscolumns with no success, created the database (CrashTestDummy) for that purpose. The system comes back with "CREATE TRIGGER permission denied on object 'syscolumns', database 'CrashTestDummy', owner 'dbo'." I had reconfigured to Allow Updates to system tables. To continue the experiment, I created a view (_syscolumns) to point to syscolumns, and the trigger came back with Invalid object name 'dbo._syscolumns'.
Creating a view on syscolumns was not a problem.
So 2000 apparently doesn't like you adding triggers to system tables (no big surprise there!), which would seem to indicate that DDL triggers in 2000 are a non-starter.
I'll try it in 2005 and let you know what happens.
-----
[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]
October 7, 2008 at 3:08 pm
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.
October 7, 2008 at 3:36 pm
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.
October 7, 2008 at 3:37 pm
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.
😀
-----
[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]
October 7, 2008 at 4:15 pm
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!
-----
[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]
October 7, 2008 at 4:49 pm
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[/url] here on SSC, so maybe it's possible, perhaps I just need to work at it a bit more.
😀
-----
[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]
March 30, 2009 at 1:22 pm
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
March 31, 2009 at 7:45 am
What is the declaration of your trigger?
By the NULLs, it appears that nothing is being pulled from EVENTDATA()
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply