MS SQL Audit Table Tool

A Go application for compliance management that connects to MS SQL Server, discovers audit-related tables, counts their records, and generates commands to empty them.

Table of Contents

• Features
• Prerequisites
• Installation
• Configuration
• Usage
• Audit Table Detection
• Output Example
• Security Considerations
• Troubleshooting

Features

• 🔌 Connects to MS SQL Server using the official Microsoft Go driver
• 🔍 Automatically discovers audit-related tables by pattern matching
• 📊 Counts rows in each audit table using system partition metadata
• 📋 Generates TRUNCATE TABLE commands for emptying tables
• 🔄 Provides alternative DELETE statements for tables with foreign key constraints
• ⚠️ Warns about large tables that may need batched deletion
• 📈 DMV Audit Information - Displays debug-level audit information from SQL Server Dynamic Management Views:
  • Server Audit Status (sys.dm_server_audit_status)
  • Database Audit Specifications (sys.database_audit_specifications)
  • Audit Actions (sys.dm_audit_actions)
  • Server Audit Specifications (sys.server_audit_specifications)
  • Audit Performance Counters (sys.dm_os_performance_counters)
  • Recent Audit Session Activity (sys.dm_exec_sessions)

Prerequisites

• Go 1.21 or higher - For building from source
• MS SQL Server - SQL Server 2012 or later
• Database access - User with permissions to read system catalog views

Installation

Quick Build

# Clone or download the repository
cd mssql_testing

# Run the compile script (downloads dependencies and builds)
./compile.sh

Manual Build

# Download dependencies
go mod download

# Build the binary
go build -o mssql-audit-tool

# Or use go run for development
go run main.go

Configuration

Step 1: Create Configuration File

Copy the sample configuration:

cp config.sample.json config.json

Step 2: Edit Configuration

Edit config.json with your database credentials:

{
    "server": "your-server-hostname",
    "port": 1433,
    "database": "YourDatabase",
    "username": "your-username",
    "password": "your-password",
    "encrypt": "disable",
    "trust_server_certificate": true
}

Configuration Options

Option	Type	Description	Example
server	string	MS SQL Server hostname or IP address	localhost, 192.168.1.100, db.example.com
port	integer	Port number for SQL Server	1433 (default)
database	string	Target database name	MyDatabase
username	string	Database username	sa, app_user
password	string	Database password	YourSecurePassword123!
encrypt	string	Encryption setting	true, false, disable
trust_server_certificate	boolean	Trust server certificate	true or false

Connection String Examples

Local Development (no encryption):

{
    "server": "localhost",
    "port": 1433,
    "database": "TestDB",
    "username": "sa",
    "password": "YourPassword",
    "encrypt": "disable",
    "trust_server_certificate": true
}

Production with SSL:

{
    "server": "prod-db.example.com",
    "port": 1433,
    "database": "ProductionDB",
    "username": "audit_user",
    "password": "SecurePassword123!",
    "encrypt": "true",
    "trust_server_certificate": false
}

Azure SQL Database:

{
    "server": "your-server.database.windows.net",
    "port": 1433,
    "database": "AzureDB",
    "username": "admin@your-server",
    "password": "YourPassword",
    "encrypt": "true",
    "trust_server_certificate": false
}

Usage

Command Line Options

./mssql-audit-tool [options]

Options:
  -config string
        Path to configuration file (default "config.json")
  -dry-run
        Show what would be purged without actually deleting
  -force
        Skip confirmation prompt for purge/truncate operations
  -purge
        Purge all audit tables (DELETE within transaction)
  -truncate
        Truncate all audit tables (faster but cannot be rolled back)

Basic Usage (Read-Only Mode)

# Ensure config.json exists
./mssql-audit-tool

# With custom config file
./mssql-audit-tool -config /path/to/config.json

Dry Run Mode

Preview what would be deleted without making any changes:

# See what tables would be affected by purge
./mssql-audit-tool -purge -dry-run

# See what tables would be affected by truncate
./mssql-audit-tool -truncate -dry-run

Purge Mode (DELETE with Transaction)

Purges audit tables using DELETE statements within a REPEATABLE READ transaction. This can be rolled back if errors occur:

# Purge with confirmation prompt
./mssql-audit-tool -purge

# Purge without confirmation (use with caution!)
./mssql-audit-tool -purge -force

Truncate Mode (Fast, No Rollback)

Truncates audit tables using TRUNCATE TABLE statements. This is faster but cannot be rolled back:

# Truncate with confirmation prompt
./mssql-audit-tool -truncate

# Truncate without confirmation (use with caution!)
./mssql-audit-tool -truncate -force

Example Output

Successfully connected to MS SQL Server!
Database: ComplianceDB

===============================================================
                    AUDIT TABLES SUMMARY
===============================================================

Schema                         Table Name                               Row Count
---------------------------------------------------------------------------
dbo                            AuditLog                                     15234
dbo                            UserActivity                                  8756
dbo                            TransactionHistory                           45231
dbo                            SecurityEvent                                 2341
audit                          SystemLog                                    12543
---------------------------------------------------------------------------
TOTAL ROWS IN AUDIT TABLES:                                             84105

===============================================================
              COMMANDS TO EMPTY AUDIT TABLES
===============================================================

⚠️  WARNING: The following commands will DELETE ALL DATA from the tables!
   Review carefully before executing.

-- Table: [dbo].[AuditLog] (contains 15234 rows)
TRUNCATE TABLE [dbo].[AuditLog];

-- Table: [dbo].[UserActivity] (contains 8756 rows)
TRUNCATE TABLE [dbo].[UserActivity];

-- Table: [dbo].[TransactionHistory] (contains 45231 rows)
TRUNCATE TABLE [dbo].[TransactionHistory];

-- Note: Large table (45231 rows). Consider batching deletes.

-- Table: [dbo].[SecurityEvent] (contains 2341 rows)
TRUNCATE TABLE [dbo].[SecurityEvent];

-- Table: [audit].[SystemLog] (contains 12543 rows)
TRUNCATE TABLE [audit].[SystemLog];

===============================================================
ALTERNATIVE: If TRUNCATE fails due to foreign key constraints:
---------------------------------------------------------------

-- Table: [dbo].[AuditLog]
DELETE FROM [dbo].[AuditLog];

-- Table: [dbo].[UserActivity]
DELETE FROM [dbo].[UserActivity];

-- Table: [dbo].[TransactionHistory]
DELETE FROM [dbo].[TransactionHistory];
-- Note: Large table (45231 rows). Consider batching deletes.

-- Table: [dbo].[SecurityEvent]
DELETE FROM [dbo].[SecurityEvent];

-- Table: [audit].[SystemLog]
DELETE FROM [audit].[SystemLog];

===============================================================
Total audit tables found: 5
Total rows across all audit tables: 84105
===============================================================

DMV Audit Information

The tool queries SQL Server Dynamic Management Views (DMVs) to display debug-level audit information:

DMVs Queried

DMV	Description
sys.dm_server_audit_status	Returns status information about server audits
sys.database_audit_specifications	Returns database audit specification details
sys.dm_audit_actions	Returns all audit actions that can be audited
sys.server_audit_specifications	Returns server-level audit specification details
sys.dm_os_performance_counters	Returns audit-related performance counters
sys.dm_exec_sessions	Returns recent audit-related session activity

DMV Output Example

           SQL SERVER AUDIT DMV INFORMATION

--- SERVER AUDIT STATUS (sys.dm_server_audit_status) ---

  Audit Name: ServerAudit
    Audit ID: 12345
    Status: RUNNING (1)
    Status Time: 2026-01-15 10:30:00
    Log File: C:\Audit\ServerAudit_12345.sqlaudit
    File Size: 1048576 bytes

--- DATABASE AUDIT SPECIFICATIONS ---

  Specification: DatabaseAuditSpec
    Audit Name: ServerAudit
    Created: 2025-12-01 08:00:00
    Modified: 2026-01-10 14:30:00
    Enabled: true

--- AUDIT ACTIONS (sys.dm_audit_actions) ---
Action ID    Action Name                   Class               Permission
--------------------------------------------------------------------------------
AED          ALTER ANY DATABASE EVENT      DATABASE            ALTER ANY DATABASE
AL           ALTER                         SCHEMA              ALTER
BK           BACKUP                        DATABASE            BACKUP DATABASE
...

--- SERVER AUDIT SPECIFICATIONS ---

  Specification: ServerAuditSpecification
    Audit Name: ServerAudit
    Enabled: true
    Created: 2025-12-01 08:00:00
    Modified: 2026-01-10 14:30:00

--- AUDIT-RELATED PERFORMANCE COUNTERS ---
Object                        Counter                        Instance             Value
----------------------------------------------------------------------------------------------------
SQLServer:Audit               Audit Records                  _Total                  5000
SQLServer:Audit               Audit Records Filtered         _Total                    50

--- RECENT AUDIT-RELATED SESSION ACTIVITY ---
Session     Login                        Host                Status     Last Activity
----------------------------------------------------------------------------------------------
57          sa                           workstation1       running    2026-02-19 14:30:00

Required Permissions for DMV Queries

To see full DMV information, the user needs:

• VIEW SERVER STATE permission for server-level DMVs
• VIEW DATABASE STATE permission for database-level DMVs

If permissions are insufficient, the tool will display a note and continue with available data.

Audit Table Detection

The tool identifies tables with names containing the following patterns (case-insensitive):

Pattern	Example Table Names
audit	AuditLog, UserAudit, audit_trail
log	ErrorLog, SystemLog, login_log
history	OrderHistory, PriceHistory
trail	AuditTrail, transaction_trail
event	SecurityEvent, SystemEvent
activity	UserActivity, LoginActivity
tracking	TrackingRecord, user_tracking
transaction	TransactionHistory, transaction_log
record	AccessRecord, change_record
archive	AuditArchive, log_archive

Security Considerations

Password Storage

⚠️ Important: The config.json file contains plaintext passwords. Consider:

1. File Permissions: Restrict access to the configuration file

   chmod 600 config.json

2. Environment Variables: Consider modifying the code to read credentials from environment variables for production use

3. Don't Commit: Add config.json to .gitignore

   echo "config.json" >> .gitignore

Database Permissions

The database user needs:

• SELECT permissions on system catalog views (sys.tables, sys.schemas, sys.partitions)
• No DELETE or TRUNCATE permissions required (the tool only generates commands, doesn't execute them)

Troubleshooting

Connection Issues

Error: "Failed to connect to database"

1. Verify SQL Server is running and accessible
2. Check firewall rules allow port 1433
3. Verify credentials are correct
4. For Docker SQL Server, ensure the container is running:

   docker ps | grep mssql

Error: "certificate verify failed"

Set trust_server_certificate to true for development:

{
    "trust_server_certificate": true
}

No Tables Found

If no audit tables are found:

1. Verify the database name in config.json
2. Check if tables exist with different naming patterns
3. Verify the user has permissions to read system catalog views

Build Issues

Error: "go: command not found"

Install Go from https://golang.org/dl/

Error: "cannot find package"

Run the compile script which handles dependencies:

./compile.sh

Project Structure

mssql_testing/
├── compile.sh          # Build script
├── config.sample.json  # Sample configuration
├── config.json         # Your configuration (create from sample)
├── go.mod              # Go module definition
├── go.sum              # Dependency checksums
├── main.go             # Application source
├── mssql-audit-tool    # Compiled binary
└── README.md           # This file

Dependencies

• github.com/microsoft/go-mssqldb - Official Microsoft SQL Server driver for Go

License

MIT License - See LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.