SQL Server Database Audit used with a trigger to monitor CREATE TABLE events

0 votes
0 answers
124 views
sql-server sql-server-2016 audit ddl-trigger
                          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


                        
Asked by Razvan Zoitanu (1004 rep)
Mar 8, 2024, 03:40 PM
Last activity: Mar 11, 2024, 01:27 PM
SQL Server Database Audit used with a trigger to monitor CREATE TABLE events

Related Questions
