0

Got SQL Audit enabled on a database with SQL Server 2016 SP1. Trying to catch a CREATE TABLE event with a trigger, and add that table to the audit to log data operations.

Problem is that changes to the state of the db audit can't be made from a trigger or by calling a sproc. Got this: "ALTER AUDIT SPECIFICATION statement cannot be used inside a user transaction."

How to get this done ? Please see below how to reproduce my experience so far.

--1. create the server audits

USE [master]

CREATE SERVER AUDIT [Server_Schema]
TO FILE 
(   FILEPATH = N'K:\SQLAudit\RZ_test\'
    ,MAXSIZE = 1024 MB
    ,MAX_ROLLOVER_FILES = 10
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '00c374df-aaaa-0000-a123-64907441fef5')

ALTER SERVER AUDIT [Server_Schema] WITH (STATE = ON)

CREATE SERVER AUDIT [Server_DML]
TO FILE 
(   FILEPATH = N'K:\SQLAudit\RZ_test\'
    ,MAXSIZE = 1024 MB
    ,MAX_ROLLOVER_FILES = 20
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '11c374df-bbbb-4444-a123-12345441fef5')

ALTER SERVER AUDIT [Server_DML] WITH (STATE = ON)


--2. create a test table
USE [testdb]
DROP TABLE IF EXISTS [dbo].[RZ_test_t1]
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]


--3. create the db audits within same db
--to catch schema changes (such as CREATE TABLE)
CREATE DATABASE AUDIT SPECIFICATION [SCHEMA_OBJECT_CHANGE_GROUP] FOR SERVER AUDIT [Server_Schema]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
--to catch data ops (DEL/INS/UPD)
CREATE DATABASE AUDIT SPECIFICATION [DML] FOR SERVER AUDIT [Server_DML]
ADD (DELETE ON OBJECT::[dbo].[RZ_test_t1] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[RZ_test_t1] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[RZ_test_t1] BY [public])
WITH (STATE = ON)


--4. run some data ops on test table
INSERT INTO [dbo].[RZ_test_t1] (c1) VALUES (1)
INSERT INTO [dbo].[RZ_test_t1] (c1) VALUES (2)
UPDATE [dbo].[RZ_test_t1] SET c1 = 0 WHERE c1 = 1
DELETE FROM [dbo].[RZ_test_t1] WHERE c1 = 2
SELECT c1 FROM [dbo].[RZ_test_t1]
TRUNCATE TABLE [dbo].[RZ_test_t1]
SELECT COUNT(c1) cnt FROM [dbo].[RZ_test_t1]


--5. open server audit [Server_DML] log and check ops on step 4 were logged ok, via SSMS GUI


--6. drop and recreate table (step 2 again)
DROP TABLE IF EXISTS [dbo].[RZ_test_t1]
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]


--7. script out the database audit [DML] and confirm the test table audit part was dropped out of that when table dropped, 
-- and not added back to audit when table recreated (should look as below).

/*
CREATE DATABASE AUDIT SPECIFICATION [DML]
FOR SERVER AUDIT [Server_DML]
WITH (STATE = ON)
GO
*/


--8. run step 4 (data ops) again, nothing will come up in the DML log, as expected.


--9. create a trigger in same db, to catch the CREATE for the test table, that should allow for the table to be audited again.
CREATE TRIGGER [RZ_trigger1] ON DATABASE AFTER CREATE_TABLE AS 
BEGIN
    ALTER DATABASE AUDIT SPECIFICATION [DML]
    ADD (DELETE ON OBJECT::[dbo].[RZ_test_t1] BY [public]),
    ADD (INSERT ON OBJECT::[dbo].[RZ_test_t1] BY [public]),
    ADD (UPDATE ON OBJECT::[dbo].[RZ_test_t1] BY [public])
END
GO


--10. drop and recreate table (step 2 again) and see the error "Changes to an audit specification must be done while the audit specification is disabled."
DROP TABLE IF EXISTS [dbo].[RZ_test_t1]
GO
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]
GO


--11. change the trigger (required table to exist) and only have it disable the audit for now
DROP TRIGGER IF EXISTS [RZ_trigger1] ON DATABASE
GO
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]
GO
CREATE TRIGGER [RZ_trigger1] ON DATABASE AFTER CREATE_TABLE AS 
ALTER DATABASE AUDIT SPECIFICATION [DML] WITH (STATE=OFF)
GO


--12. drop and recreate table (step 2 again) and see another error "ALTER AUDIT SPECIFICATION statement cannot be used inside a user transaction."
DROP TABLE IF EXISTS [dbo].[RZ_test_t1]
GO
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]
GO


--13. create a sproc to disable the audit
CREATE OR ALTER PROCEDURE [dbo].[usp_AlterSQLAudit] 
AS ALTER DATABASE AUDIT SPECIFICATION [DML] WITH (STATE=OFF)
GO


--14. change the trigger (requires table to exist) to use the sproc
DROP TRIGGER IF EXISTS [RZ_trigger1] ON DATABASE
GO
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]
GO
CREATE TRIGGER [RZ_trigger1] ON DATABASE AFTER CREATE_TABLE AS 
EXEC [dbo].[usp_AlterSQLAudit]
GO



--15. drop and recreate table (step 2 again) and see again error "ALTER AUDIT SPECIFICATION statement cannot be used inside a user transaction."
DROP TABLE IF EXISTS [dbo].[RZ_test_t1]
GO
IF OBJECT_ID(N'dbo.RZ_test_t1', N'U') IS NULL CREATE TABLE [dbo].[RZ_test_t1] (c1 INT NULL) ON [PRIMARY]
GO
6
  • Why do you put table names in your DATABASE AUDIT SPECIFICATION while you can use schema name for example dbo
    – sepupic
    Commented Mar 8 at 16:37
  • I only need to look at data changes for 1-2 specific tables, out of hundreds in the same schema Commented Mar 8 at 16:59
  • Ok, you can put only the tables of your intereset that you catch in ddl-trigger into some table and create a job that controls every minute(5 minutes,ecc) that table and if a new table of your interest is there add it to your audit specification and mark this row in that table as "done" or just delete it as already processed
    – sepupic
    Commented Mar 8 at 17:43
  • Won't work in my scenario. There's a lot of ETL involved, so I need to start the audit right after the table is recreated. Waiting for 30s might be too long and queries might be missed. Commented Mar 8 at 20:10
  • 1
    This sounds like an XY Problem. Why not put those few tables into their own schema and plase the audit on that schema? Or why not have the code that creates the tables place the audit? Commented Mar 10 at 1:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.