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; MySQL/MariaDB
SHOW PROCESSLIST; -- Active sessions Oracle
SELECT * FROM v$session; 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; 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; 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." 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 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!
Dear author, I started applying the 1st scripts, but getting errors as CURRENT_DATE does not exist in sql server 2016 and 2019.
Same thing for information_schema.tables which has no last_update field. I ran
SELECT *
FROM information_schema.tables
I get
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
no table_name nor last_update fields
So I stopped applying and waith to see if you can help