November 15, 2018 at 10:00 am
I am trying to get the query executed to perform an insert, update or delete on a table, but only if it is not performed by a specific Service Account .
I have created the trigger below :
USE [DW_test]
GO
/****** Object: Trigger [dbo].[AuditTrigger] Script Date: 11/15/2018 11:56:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[AuditTrigger]
ON [dbo].[TESTTABEL]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @USER varchar(500) = SYSTEM_USER
DECLARE @sqltext VARBINARY(128)
DECLARE @SPID int = @@SPID
IF( @USER = 'ServiceAccount')
Begin
WAITFOR DELAY '00:00:00'
END
ELSE
BEGIN
INSERT AuditTable (UserName,Query)
SELECT SYSTEM_USER , Sqltext.TEXT
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
END
END
GO
ALTER TABLE [dbo].[TESTTABEL] ENABLE TRIGGER [AuditTrigger]
GO
However, it just places the create script for the trigger in the audit table. What is the correct way to go about finding the query that is being used to update, inert or delete in a trigger ?
November 15, 2018 at 12:06 pm
Here's what I typically preset triggers with.
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Exit early if no changes where actually made to the base table.
-- ***** DO NOT PUT ANY CODE ABOVE THIS LINE OR YOU WILL PROGRAMATICALLY DISABLE THE TRIGGER!!! *****
IF @@ROWCOUNT = 0 RETURN
;
--===== Suppress row count returns to prevent them from being mistaken as errors.
SET NOCOUNT ON
;
--===== Determine which operation fired this trigger.
DECLARE @Operation CHAR(1) = CASE
WHEN NOT EXISTS (SELECT * FROM DELETED) THEN 'I' --Inserts have no DELETED rows
WHEN NOT EXISTS (SELECT * FROM INSERTED) THEN 'D' --Deletes have no INSERTED rows
ELSE 'U' --Updates have both.
END
;
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2018 at 12:09 pm
BTW... I believe that you'll find that capturing the query and saving it in the audit table will become a HUGE distraction not to mention a pretty good drag on performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2018 at 1:01 pm
Jeff, under normal circumstances I would agree, The issue is that the table is a massive table that is updated frequently by the application, so our normal audit tools just generate a ton of noise. The idea here is just to log what somebody does 1-2x a month when an issue requires manual admin intervention. The script is to validate to the auditors that what was listed in the ticket for the issue is what was run in actuality. The admins at that site will not have access to the service account credential, only to their own elevated privilege accounts with AD authentication, so we can tie the user & the query to the issue ticket. That is why the trigger has the IF( @USER = 'ServiceAccount')
Begin
WAITFOR DELAY '00:00:00'
at the start of the trigger.
November 15, 2018 at 1:32 pm
Fair enough. What is the WAITFOR DELAY for? It's waiting for zero seconds.
Also, was the @Operation thing in the code I posted what you were looking for?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2018 at 3:43 pm
Something like this might work:USE [DW_test]
GO
/****** Object: Trigger [dbo].[AuditTrigger] Script Date: 11/15/2018 11:56:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[AuditTrigger]
ON [dbo].[TESTTABEL]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @USER varchar(500) = SYSTEM_USER
DECLARE @sqltext VARBINARY(128)
DECLARE @SPID int = @@SPID
IF( @USER = 'ServiceAccount')
Begin
WAITFOR DELAY '00:00:00'
END
ELSE
BEGIN
INSERT AuditTable (UserName,Query)
SELECT TOP(1) SYSTEM_USER , Sqltext.TEXT
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS Sqltext
WHERE Sqltext.text like '%TESTTABEL%'
AND Sqltext.text not like '%!%TESTTABEL!%%' escape '!'
ORDER BY Sqltext.last_execution_time DESC
END
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply