May 8, 2008 at 7:10 am
Hi All,
For audit reasons I need to save information about some database events (CREATE, ALTER, DROP) into a central table, for that I've deployed a Server Trigger that rec the information in a Linked Server table, but fails.
I've checked the trigger using a local table instead a linked server table, and runs correct, but when I change the table source by linked server table fails again.
Do you have any idea to solve?
Thanks in advance.
This is the Server trigger
/****** Object: DdlTrigger [ServerDBEvent] Script Date: 05/08/2008 15:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ServerDBEvent]
ON ALL SERVER
for CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE
AS
SET NOCOUNT ON
DECLARE @data AS xml
DECLARE @EventType AS varchar(25)
DECLARE @PostTime AS varchar(25)
DECLARE @ServerName AS varchar(25)
DECLARE @DBName AS varchar(150)
DECLARE @login AS varchar(50)
DECLARE @TSQLCommand AS varchar(MAX)
DECLARE @idnt as int
DECLARE @msg as varchar(50)
DECLARE @comp as bit
SET @msg='completed'
SET @data = EVENTDATA()
SELECT @EventType =
@data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(25)')
, @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','varchar(25)')
, @ServerName =
@data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(25)')
, @login = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(25)')
, @DBName =
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(25)')
, @TSQLCommand =
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)')
if @EventType='DROP_DATABASE'
begin
raiserror ('Only DBA team can drop any database, please contact with us to do.
If you are a DBA member you must first disable ServerDBevent trigger to drop a database',16,1)
set @comp=0
rollback
end
else
begin
Print 'Hi ' + @login + ', your has been processed and its status is: ' + @msg
set @comp=1
end
INSERT INTO [SQLSRV1001\AUDIT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG]
([ENV_SRV_INS_DB_CHG_Server]
,[ENV_SRV_INS_DB_CHG_Name]
,[ENV_SRV_INS_DB_CHG_EventType]
,[ENV_SRV_INS_DB_CHG_PostTime]
,[ENV_SRV_INS_DB_CHG_Login]
,[ENV_SRV_INS_DB_CHG_TSQLCommand]
,[ENV_SRV_INS_DB_CHG_Completed])
values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)
RETURN
SET NOCOUNT OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ServerDBEvent] ON ALL SERVER
Francisco Racionero
twitter: @fracionero
May 8, 2008 at 7:22 am
Trigger actions are limted to the local server.
Replace you're ddl trigger with an event notification. You can then send message to anther server using service broker.
[font="Verdana"]Markus Bohse[/font]
May 8, 2008 at 7:23 am
-We record this at the sqlinstance itself and only grant insert access to that particular table of our "admin" db.
- This just to avoid having issues if your central audit server is down, ..
- We gather the data to a central server every week.
[remove this]
If you still want to use the linked server solution (and hook everything up to your audit server):
- Did you create the linked server ?
- what security did you setup to be used with the linked server ?
[/remove this]
As MarkusB stated, triggers only work local.
If you still try to create a trigger to a remote db, you'll get an error like:
Msg 8624, Level 16, State 1, Procedure ddlDatabaseTriggerLog, Line 32
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
In this case I've setup a ddl-trigger to insert into a remote db, made sure all connections were handled by sql-userid RmtAudit which has select/insert/viewdefinition rights for the target table;
Classic insert works, as long as you don't use an XML datatype (not supported for remote queries ??!!?? )
Once encapsulated in a ddl-trigger, I receive Msg 8624.
Service broker may be an option, but may fail for the same reasons as you are wanting to set this system up using a central server.
i.e. avoid local tampering :unsure:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply