Double registration when using select into

3 votes
0 answers
94 views
sql-server sql-server-2019 audit select-into
                          SQL Server 2019 CU15, Database Audit:

When auditing a SELECT on a database, everything looks fine, 
but if I SELECT INTO a #myTempDB, I get double registration in the AUDIT LOG.

I created a test database for this purpose, and configured database auditing to monitor SELECT on my test database.


    USE [master]

    GO
    Drop database if exists [AuditTestDatabase];
    Create Database [AuditTestDatabase];
    Use [AuditTestDatabase]
    go
    Create Table AuditAuditTest (N integer)
    insert into AuditAuditTest (N) Values(1)
    Select * From AuditAuditTest
    
     Create Audit output specification (server audit)
    (Create the Audit in your Temp)
    
    Use [master]
    If exists (SELECT 1 FROM sys.server_audits where name='AuditFiles')
    Begin
      ALTER SERVER AUDIT [AuditFiles] WITH (STATE = OFF);
      DROP SERVER AUDIT [AuditFiles];
    End
    Go
    CREATE SERVER AUDIT [AuditFiles]
    TO FILE 
    (           FILEPATH = N'C:\Temp\Audit\'
                ,MAXSIZE = 10 MB
            ,MAX_ROLLOVER_FILES = 2147483647
            ,RESERVE_DISK_SPACE = OFF
    ) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = 'b630ef61-59c4-4318-83db-eee587c89fbe')
    ALTER SERVER AUDIT [AuditFiles] WITH (STATE = ON)
    GO
    
    Create Database Audit
    USE [AuditTestDatabase]
    GO
    If exists (SELECT 1 FROM sys.database_audit_specifications where name='DatabaseAuditSpecification')
    Begin
      ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification] WITH (STATE = OFF);
      DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification];
    End
    Go
    CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]
    FOR SERVER AUDIT [AuditFiles]
    ADD (SELECT ON DATABASE::[AuditTestDatabase] BY [public])
    WITH (STATE = ON)
    GO

Auditing is now running.

This can be verified by opening the Audit folder, where there should be one, and only one, sqlaudit file.

 

**Viewing Audit Logs from SSMS**

 

At this point, it only has information that auditing has started.

Next, I try this

    Select * From [AuditTestDatabase].dbo.AuditAuditTest

As expected, the SELECT is now in the audit log

Next, I try

    Select * Into #L From [AuditTestDatabase].dbo.AuditAuditTest
    Drop Table #L

And take another look at Audit log.
 
The SELECT is now present 3 times, this happens every time I select into a temp table.

Is this a bug, or is there something I don’t see?

                        
Asked by Torben iisager (31 rep)
Feb 7, 2022, 01:17 AM
Last activity: Feb 7, 2022, 12:50 PM
Double registration when using select into

Related Questions
