SQL Server Database Auditing: Daily, Weekly, and Monthly Best Practices

Introduction

Database auditing is a critical practice for maintaining security, compliance, and performance in SQL Server environments. By tracking user logins, schema changes, stored procedure executions, and failed login attempts, DBAs can detect suspicious activities and optimize database performance.

In this guide, we’ll explore SQL scripts and shell scripts to automate daily, weekly, and monthly database audits in SQL Server, MySQL/MariaDB, and Oracle.

Why Database Auditing is Essential

Auditing helps:
✔ Enhance security by tracking unauthorized access.
✔ Improve compliance with regulatory standards (GDPR, HIPAA, SOX).
✔ Optimize performance by identifying inefficient queries.
✔ Troubleshoot issues by reviewing historical database activity.

SQL Scripts for Database Auditing

1. Daily Audit Queries

Track daily activities to detect anomalies quickly.

SQL Server

-- Daily logins  
SELECT user_name, login_time  
FROM sys.dm_exec_sessions  
WHERE login_time >= CURRENT_DATE - 1  
ORDER BY login_time DESC;  

-- Modified tables  
SELECT table_name, last_update  
FROM information_schema.tables  
WHERE last_update >= CURRENT_DATE - 1  
ORDER BY last_update DESC;  

-- Executed stored procedures  
SELECT procedure_name, last_execution  
FROM sys.procedures  
WHERE last_execution >= CURRENT_DATE - 1  
ORDER BY last_execution DESC;  

-- Failed logins  
SELECT login_name, failed_login_attempts  
FROM sys.logins  
WHERE failed_login_attempts > 0  
ORDER BY failed_login_attempts DESC;

SQL

MySQL/MariaDB

SHOW PROCESSLIST;  -- Active sessions

SQL

Oracle

SELECT * FROM v$session;

SQL

2. Weekly Audit Queries

Monitor weekly trends for security and performance.

SQL Server

-- Weekly logins  
SELECT user_name, login_time  
FROM sys.dm_exec_sessions  
WHERE login_time >= CURRENT_DATE - 7  
ORDER BY login_time DESC;  

-- Modified tables  
SELECT table_name, last_update  
FROM information_schema.tables  
WHERE last_update >= CURRENT_DATE - 7  
ORDER BY last_update DESC;  

-- Executed stored procedures  
SELECT procedure_name, last_execution  
FROM sys.procedures  
WHERE last_execution >= CURRENT_DATE - 7  
ORDER BY last_execution DESC;  

-- Failed logins  
SELECT login_name, failed_login_attempts  
FROM sys.logins  
WHERE failed_login_attempts > 0  
ORDER BY failed_login_attempts DESC;

SQL

3. Monthly Audit Queries

Long-term auditing for compliance and trend analysis.

SQL Server

-- Monthly logins  
SELECT user_name, login_time  
FROM sys.dm_exec_sessions  
WHERE login_time >= CURRENT_DATE - 30  
ORDER BY login_time DESC;  

-- Modified tables  
SELECT table_name, last_update  
FROM information_schema.tables  
WHERE last_update >= CURRENT_DATE - 30  
ORDER BY last_update DESC;  

-- Executed stored procedures  
SELECT procedure_name, last_execution  
FROM sys.procedures  
WHERE last_execution >= CURRENT_DATE - 30  
ORDER BY last_execution DESC;  

-- Failed logins  
SELECT login_name, failed_login_attempts  
FROM sys.logins  
WHERE failed_login_attempts > 0  
ORDER BY failed_login_attempts DESC;

SQL

Automating Audits with Shell Scripts

For Linux-based DBAs, shell scripts can automate audits via sqlplus or sqlcmd.

Example: Daily Audit Script (`daily_audit.sh`)

#!/bin/bash  

echo "Running daily audit..."  

# Get today's logins  
sqlplus -S user/password @daily_audit.sql > daily_audit.log  

# Get modified tables  
sqlplus -S user/password @daily_audit_tables.sql >> daily_audit.log  

# Get executed procedures  
sqlplus -S user/password @daily_audit_procedures.sql >> daily_audit.log  

# Get failed logins  
sqlplus -S user/password @daily_audit_logins.sql >> daily_audit.log  

echo "Daily audit completed. Results saved to daily_audit.log."

SQL

Scheduling with Cron

# Run daily at midnight  
0 0 * * * /path/to/daily_audit.sh  

# Run weekly on Sundays  
0 0 * * 0 /path/to/weekly_audit.sh  

# Run monthly on the 1st  
0 0 1 * * /path/to/monthly_audit.sh

SQL

Best Practices for Database Auditing

🔹 Schedule audits regularly (daily, weekly, monthly).
🔹 Store logs securely (encrypted storage, restricted access).
🔹 Review logs proactively to detect breaches early.
🔹 Automate alerts for suspicious activities (e.g., multiple failed logins).

Conclusion

Database auditing is a must-have for security-conscious DBAs. By leveraging SQL scripts and automation, you can efficiently monitor database activities and mitigate risks.

📌 Want more SQL Server tips? Subscribe to OurTechIdeas for weekly updates!