February 28, 2011 at 12:38 pm
I'm having problems getting a DB Trigger on DDL to work. From what I've read and discussed with GSquared at a prior thread (http://www.sqlservercentral.com/Forums/Topic1012487-361-1.aspx ) this should work but it does not.
Currently my trigger is defined as:
SET ANSI_NULLS ON
GO
SET ARITHABORT ON
GO
SET ANSI_WARNINGS ON
go
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DB_DDL_LogDDLEvents_TrgWD] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;
BEGIN
SELECT 1
END
GO
With the trigger enabled the application we use raises the following error when it tries to create a view ( which the application does to manage security):
Multi-step OLE DB Operation generated errors. Check each OLE DB status value, if available. No work was done.
In the previous thread with GSquared I mentioned that 3 SET options are all disabled by the accounting application we use and that these 3 were listed in BOL as being required but they are supposed to be required as ON only when you create the Trigger and so I explicitly turned these on when creating the trigger but that however did not change the outcome and the message persists.
The 3 SET options the app disables are:
ARITHABORT
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
Can anyone take a shot at why this is causing problems? It's frustrating as hell that every avenue I take to try and implement auditing on this DB is thrown off by this darn application. We are using SQL Server 2005 and the database itself is set to a compatibility mode of SQL Server 2000 and I don’t know if that’s part of the problem or not but I'm mentioning it just in case.
One last note in case it helps. When I trace the app both when the trigger is enabled and again when it is not, while performing the same function in teh applictaion, the line in Profiler that works when the trigger is disabled and that frist fails when the triogger is enabled is a call to the SP 'sp_cursoropen'.
Thoughts?
Kindest Regards,
Just say No to Facebook!February 28, 2011 at 12:56 pm
I just tried this on an SQL 2005 server:
USE ProofOfConcept;
GO
SET ANSI_NULLS ON
GO
SET ARITHABORT ON
GO
SET ANSI_WARNINGS ON
go
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DB_DDL_LogDDLEvents_TrgWD] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON;
BEGIN
SELECT 1;
END;
In another connection:
SET ANSI_NULLS OFF
GO
SET ARITHABORT OFF
GO
SET ANSI_WARNINGS OFF
go
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SET QUOTED_IDENTIFIER OFF
go
CREATE VIEW DropMe4
AS
SELECT 2 AS Col;
The trigger fired without errors.
I tried creating a Compat 80 database, creating the same trigger and view in it, and the trigger still created and fired without error.
I think you may be running into something other than the settings on the connection being a problem.
Try changing the trigger from a Select to a level 10 Raiserror.
RAISERROR('Trigger Fired', 10, 1);
Try running a Create View statement in the database from a Management Studio connection, instead of the application doing it.
If none of those work, you'll probably need to move over to passive logging/auditing. That means traces, instead of triggers, in this case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply